How to query data from an opened Excel workbook without AdvancedFilter?

[feipezi]

Hi,

I'm try to get retrieve some data from the workbook I'm working on, using ADO, since I don't like AdvancedFilter feature. If I give the path of the workbook, fine; but I am trying to use NamedRange instead of the workbook itself. It looks like it's not working.

I got trouble code like 'run-time error '2147467259(80004005)'; cannot find "H:\data\range(statekeyacctmetrics)...."

The H: drive is a network drive and has nothing to do with the data whatsoever. I am lost.

Thanks in advance.

here is the code:


Sub GetData_From_NamedRange()
Dim jqzConnect As String
Dim jqzRecordset As ADODB.Recordset
Dim jqzTable As ADODB.Recordset
Dim jqzSQL As String
Dim tln As String
Dim brand As String
Dim metric As String
Dim dummy As String

dummy = "StateKeyAcctMetrics"
jqzConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & "Range(" & dummy & ")" & "Extended Properties=Excel 12.0"
tln = "HI"
brand = "ARCAPTA"
metric = "MKT"

jqzSQL = "SELECT * FROM [Range(dummy)$]" & _
" WHERE tln like '" & tln & "%'" & "and prod_grpdesc='" & brand & "' and metric like '" & metric & "%'" & _
"order by sumkeyaccttc desc"

Set jqzRecordset = New ADODB.Recordset
jqzRecordset.Open jqzSQL, jqzConnect, adOpenStatic, adLockReadOnly

Set jqzTable = New ADODB.Recordset
jqzTable.Open jqzSQL, jqzConnect
Sheet5.Cells(2, 1).CopyFromRecordset jqzTable
End Sub

 

[PHV]

Why not simply use MS-Query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Using MS Query to get data from Excel faq68-5829

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}