I'd like to read an Excel 2003 (.xls) (not xlsx) workbook, and at this point I am wondering if it is possible.
Using the following connection string and code:
string cnstr = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=c\:mytext.xls;Extended Properties=Excel 12.0;"
DataTable dt = new DataTable();
OleDbDataAdapter cmd = new OleDbDataAdapter("SELECT * FROM [IR Database$A4:IV18] ", cnstr);
I get the message:
"The Microsoft Office Access database engine could not find the object 'IR Database$A4:IV18'. Make sure the object exisits and that you spell its name and path name correctly.
I've changed "Extended Properties=Excel 12.0" to "Extended Properties=Excel 8.0" and get the same result.
The Jet connection string works fine, but not on a 64 bit Server (via a CLR).
Interestingly the OpenRowSet in SQL works fine ...
SELECT * Into #tempPool FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=\\server\Pool.xls;ReadOnly=True;IMEX=1', SELECT * FROM [FacilityPoolMap$]')
Makes me wonder if I need to install the 64x ACE Driver (installed on the server) on my development machine.
Suggestions are appreciated.