Hello,

I am trying to retrieve data from a workbook (xlsx) through ADO.

It works fine with ranges and workbook-level named ranges.

For instance, say that the workbook Hello.xlsx contains a range named "AnyData"
(defined at workbook level). This code works fine:

Const File = "C:\Temp\Hello.xlsx"
Dim Rs As New ADODB.Recordset
Dim Conn As New ADODB.Connection
Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & File & _
";Extended Properties=""Excel 12.0;HDR=NO;IMEX=1"""
Rs.Open "SELECT * FROM AnyData", Conn

Now, suppose that "AnyData" is a worksheet-level name (attached to Sheet1, for
instance). How do you pass Sheet1!AnyData to the SELECT query?

I've tried :

Rs.Open "SELECT * FROM [Sheet1$AnyData]", Conn

.... but this query is rejected by the Jet engine.

If you do as if AnyData were a workbook-level name :

Rs.Open "SELECT * FROM AnyData", Conn

.... it works fine, but in this case, how can I access for instance another
"AnyData" named range which would be attached to another worksheet (say for
instance Sheet2!AnyData)?

Thanks !