Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Accessing data stored in the worksheet (named range) in .xla file | Excel Programming | |||
accessing named range | Excel Programming | |||
Named range in a sheet referred to from another sheet | Excel Programming | |||
Access a worksheet level named range with Refers To like "=5". | Excel Programming | |||
Why, when I create workbook-level name does it jump it to Sheet-level ? | Excel Programming |