CODEWITHSUNDEEP
excelvbaconnection-stringado
Jim Snyder
Jim Snyder

Reputation: 39

Excel 2010 VBA/ADO connection error with XL source and text output

I am using Excel 2010 with a macro to access another daily spreadsheet to pull data for forming an FTP file of records. The specific problem I am having is a runtime connection error. The error I am getting is '-2147467259(80004005)': Unrecognized database format 'C:\Work\Daily FTP Process\Excel DBs and Files\ftp.xlsx'. All I need it to know where to look. Here is the connection string from the watch:

: ConnectionString : "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Work\Daily FTP >Process\Excel DBs and Files\ftp.xlsx;" : String : Module1.XLFixedFieldFile

Here is the pertinent (or impertinent) code:

Dim conn As Object
Dim cmd As Object
Dim psidRecSet As Object
Dim loopIndex As Long
Dim connString As String
Dim sqlString As String

Set conn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")

connString = "Provider=Microsoft.ACE.OLEDB.12.0;" _
   & "Data Source=" & XLName & ";"
conn.Open connString    <==== Here is the line where it is breaking

Upvotes: 1

Views: 8338

Answers (2)

Jim Snyder
Jim Snyder

Reputation: 39

SOLVED! The connectionstring that worked is as follows: 

    connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & XLName & 
    "';Extended Properties='Excel 12.0;HDR=NO;IMEX=1';"

Remou, I had tried that, but had something wrong and got an error. The copy&paste of a working line found in another forum was my salvation. I appreciate the help.

For others using this yto solve their problems, to work with Excel 2010, I went to "Tools/Reference" and enabled Microsoft AcriveX Data Objects 6.1 Library and Microsoft ActiveX Data Objects Recordset 6.0 Library. 

    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & XLName & 
    "';Extended Properties='Excel 12.0;HDR=NO;IMEX=1';"
    conn.Open connString

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91376

You want:

 connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _ 
 & xlFile & ";Extended Properties='Excel 12.0 Xml;HDR=No;IMEX=1';"

Note the quotes for the extended property.

You might like to read http://support.microsoft.com/kb/257819, with particular reference to IMEX.

Upvotes: 0

Related Questions