VBA ADO connection to .xlsx file

Question

I am trying to copy data from a closed Excel 2007 workbook (.xlsx) using an ADO connection.

I have the connection string working. But I get an automation error when I try to open the Command in the Recordset (Second to last line).

This may not be clear in the below code so:

"wsSummary" is a worksheet object "strSourceFile" is a string with the target data I need to copy from (e.g. Template.xlsx)

strSourceFile = wsSummary.Cells(nFirstRow + 4, 7)
strSheetSource = "Sheet1"
strSQL = "SELECT * FROM [" & strSheetSource & "]"

Set dbConnection = New ADODB.Connection
With dbConnection
    .Provider = "Microsoft.ACE.OLEDB.12.0;"
    .connectionString = "Data Source=" & strPOINTDataPath & strSourceFile & _
                        ";Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"";"
    .ConnectionTimeout = 40
    .Open
End With
If dbConnection = "" Then GoTo ErrorText

Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = dbConnection
    .CommandText = strSQL
End With

Set rs = New ADODB.Recordset
With rs
    .ActiveConnection = dbConnection
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open cmd
End With

Kazimierz Jawor Kazimierz Jawor · Accepted Answer · 2013-08-09T10:46:10

I think you missed $ character in your SQL statement. Try to change appropriate line into this one:

strSQL = "SELECT * FROM [" & strSheetSource & "$]"

or change strSheetSource variable into this:

strSheetSource = "Sheet1$"

VBA ADO connection to .xlsx file

1 Answers