SQL Server, query Excel workbook sheet count and refer by index.

From SQL Server, I'm querying Excel sheets without problem by using:
SELECT * FROM [Sheet1$]

However, in the event I don't know the sheet name, or how many there are, is there a way to query the sheet count and then refer to them by their index?

Thanks.
R_O_BAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
R_O_BConnect With a Mentor Author Commented:
I'll never import the wrong sheet. I want a raw import of every sheet. I'm moving on... right environment, wrong tool. Thanks all.
0
 
chinawalCommented:
>>From SQL Server, I'm querying Excel sheets without problem by using:
>>SELECT * FROM [Sheet1$]

How is this possible?
It seems, you exported a particular excel worksheet from a particular excel workbook into SQL Server database.
Or are you using "Linked Servers" like this http://support.microsoft.com/kb/306397?


0
 
Brad Sims, CCNANetwork AdministratorCommented:
I was wondering the same thing.  You can refer to worksheets by index in VBA, but it would just depend on how you are connecting from SQL to Excel.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
R_O_BAuthor Commented:
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]
0
 
NorieVBA ExpertCommented:
As far as I know you can't refer to the sheets by index in a query like that

Why would you want/need to anyway?

Even if you could you might end up importing the wrong worksheet - even in Excel the sheet index isn't used that often.

The sheet count might not help either - why not try the linked server?
0
 
R_O_BAuthor Commented:
...
0
All Courses

From novice to tech pro — start learning today.