OpenDataSource/OpenRowSet Excel - what's the workbook called?!

[ChainsawJoe]

I'm trying to use OpenRowSet or OpenDataSource to query an Excel file, as demonstrated here:

http://www.databasejournal.com/features/mssql/article.php/10894_3331881_1

But when using the code

SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="C:\test\Book1.xls";
    User ID=;Password=;Extended properties=Excel 8.0')...Book1$

With OpenRowSet I get the error:

Could not process object 'Select * from [Book1$]'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.

And with OpenDataSource:

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table 'ThisWorkbook$'.  The table either does not exist or the current user does not have permissions on that table.

(and it's not a user auth issue, as when I type in gibberish as the userID I get the error

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.

instead)

It's s test excel file that contains one column with values 100, 200, 300, 400. I even edited the code for the current sheet so I coulde rename the workbook to "Book1", but to now avail.

And yes, I know almost NOTHING about excel. (I only ever used it to demonstrate easter eggs - y'know the old flight sim thing.. heheh.. great fun..)

Any tips/help/pointers would be greatly appreciated!

TIA

--------------------------------------------------

http://www.chainsawjoe.com

- better than toast.
Penguins - better than

http://www.chainsawjoe.com

--------------------------------------------------

 

[ChainsawJoe]

Ah. My bad. Sorry! It's meant to be the *Sheet* name, not the workbook name... I changed it to

SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="C:\test\Book1.xls";
    User ID=;Password=;Extended properties=Excel 8.0')...[Sheet1$]

and it's all good.

--------------------------------------------------

http://www.chainsawjoe.com

- better than toast.
Penguins - better than

http://www.chainsawjoe.com

--------------------------------------------------