I am currently using ACE.OLEDB.12 for querying Excel data tables and I encountered a limitation with the provider that is frustrating. The provider can only address the first 256 columns and 65536 rows so if I position any of my data tables outside that window the engine comes back with an error message of range not found.
Does anyone know of any other data provider that will allow me to query data directly from data tables like the example below using ACE.OLEDB.12?
SELECT * FROM [Main Sheet$IG7:IU9]
I use ADODB all the time and have the same issue. Looked for an answer everywhere, even on SO, with no luck.
The only workaround I found: move your data to the beginning of the sheet. If your data starts at A1 in e.g. Sheet1, your query will pull in the additional rows and columns as long as you reference the sheet only, i.e. SELECT * FROM [Sheet1$]. I haven't tested this with ACE.OLEDB but it sure works with ADODB.
You can use ClosedXML to query XLSX files. Of course, it doesn't offer any SQL functionality (instead, it behaves similar to VBA), but has the advantage that neither Excel nor the AccessDatabaseEngine need to be installed on the target machine. Maybe it's worth a look.
