ADODB-connection to specific XLSX-sheet

[ino_mart]

All

I am using Access 2007 and I need to create an ADODB-connection to a specific Excel worksheet. The worksheet is in the XLSX-format and the installed Excel version is also 2007. The name of the worksheet is shtImport. The complete "Office 2007 Enterprise"-suite is installed on the computer. The XLSX does contain the worksheet shtImport

strOpenFilename = "c:\test\workbook.xlsx"
strSQL = "select * from shtImport"
Set cnXLS = New ADODB.Connection
cnXLS.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strOpenFilename & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Set rsXLS = New ADODB.Recordset
rsXLS.Open strSQL, cnXLS, adOpenDynamic, adLockOptimistic
rsXLS.MoveFirst

The connection to the XLSX succeeds, but on rsXLS.Open I get the errormessage: The microsoft Office Access database engine could not find the object 'shtImport'...

Can someone help me?

Regards
Ino

 

[mdlueck]

The thread here seems similar to your question, and I get the impression that the OP finally was able to do what you want to do:

"ADO connection to Excel 2007 *.xlsx files?"
http://www.officekb.com/Uwe/Forum.aspx/excel-prog/167524/ADO-connection-to-Excel-2007-xlsx-files

 

[ino_mart]

@mdlueck

This is not the same issue. In the link you provided, the connection towards the Excel file fails.

In my code, the connection does succeed. It is the SQL-command which does not succeed as shtImport cannot be found, although the sheet does exist.

Next code also fails and gives a similar error message
select * from [Sheet1$]

 

[mdlueck]

This link has code which queries the spreadsheet file for valid sheet names.

Also I see they wrap the sheet name in [].

http://code.activestate.com/recipes/440661-read-tabular-data-from-excel-spreadsheets-the-fast/