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

Question

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

Your connection string is wrong, get one here connectionstrings.com/excel-2007 — Fionnuala
Much thanks, Remou. That was a resource I hadn't found. I am still working on the connection string to get it to work, but the current state of the string is as follows: — Jim Snyder
Watch : : connString : "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Work\Daily FTP Process\Excel DBs and Files\ftp.xlsx;Extended Properties=Excel 12.0 Xml;HDR=NO;IMEX=1;" : String : Module — Jim Snyder

Jim Snyder Jim Snyder · Accepted Answer · 2013-02-07T15:48:55

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

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

2 Answers