A
Ahasverus
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 !
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 !