Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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];
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
How about:
Sheets:
SQLTables;
- Ralf
The problem is that I am getting the pop-up attached above when trying to connect, before SQLTABLES statement.
Aldo.
Because you need to set User/Password in the connect statement...
It seems that ODBC provider for Excel files is missing.
What should I install?
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
After installing the provider for Office, ODBC is running ok.
OLE DB runs also.
Thank you guys!
Aldo.