Accessing a sheet-level named range through ADO
 

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 !

Accessing a sheet-level named range through ADO
 

Solved !

The right syntax is : "SELECT * FROM [Sheet1$]AnyData"

Ahasverus a écrit :

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 !

Accessing a sheet-level named range through ADO
 

Odd, it was how you had it in 2003 with Jet provider and Excel 8.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Ahasverus" wrote in message
...

Solved !

The right syntax is : "SELECT * FROM [Sheet1$]AnyData"

Ahasverus a écrit :

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 !