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 !

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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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 !
>>