[RESOLVED] Referencing an Excel worksheet using ADO

[Guru]

I can do this sort of thing:
Select * FROM [Sheet1$]

but not if the worksheet is called 'Contracts & Licences'

What brackets etc would I need?
Renaming the worksheet would be a last resort because it is used by non-techies

Alternatively, is it possible to access a worksheet by an index value?


Thanks

[RobDog888]

Should be something like...

SELECT * FROM [Contracts & Licences$]

[RobDog888]

My post #13 in this thread shows how to open an ADO recordset object with the Excel workbook schema in order to retrieve the sheet names in the workbook. I have a few other examples throughout that thread but the OP was having some issues that may also be a bit confusing/redundant for your concerns.

Using an index position is not good as the user can add/delete sheets which could throw off the index positioning etc. If you retrieve the schema value of the sheet name then you will know exactly the name needed to reference that sheet in a second call to the sheet for selecting data from it.


http://www.vbforums.com/showthread.php?t=332610

[Guru]

Thanks! That was just the job, I needed single quotes around it

SELECT * FROM ['Contracts & Licences$']