OLE DB Connection string to Excel 2013 xlsx file - Read / retrieve list of worksheets

[aldo-tgh]

Hi Guys,

I need to read the list of worksheets in an Excel file. In this case is xlsx but I will need to deal also with xls.

I know this task can be done using Extended properties of ODBC /OLE DB connection.

In order to implement this solution, I am trying to connect to an unprotected Excel 2013 xlsx file with the connection string below, but getting the pop-up below asking for some missing information.

Any help will be appreciated.

Thanks,

Aldo.

OLEDB CONNECT32 TO [Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=$(vPath);Mode=Share Deny None;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False]; 

[rbecher]

How about:

Sheets:

SQLTables;

- Ralf

[aldo-tgh]

The problem is that I am getting the pop-up attached above when trying to connect, before SQLTABLES statement.

Aldo.

[rbecher]

Because you need to set User/Password in the connect statement...

[aldo-tgh]

It seems that ODBC provider for Excel files is missing.

What should I install?

[rwunderlich · Accepted solution]

I don't think you can use the Jet provider for xlsx.

On my machine, I have an ODBC driver for Microsoft Excel. The DSN is present in the UserDSN. I assume it installed with MS Office.

I believe you can use OLEDB as well, but you have to download a driver.

Info - Microsoft ACE OLEDB 12.0 - ConnectionStrings.com

-Rob

[aldo-tgh]

After installing the provider for Office, ODBC is running ok.

OLE DB runs also.

Thank you guys!

Aldo.