Creating QueryTable from VB6

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to create an Excel addin that retrieves query statements from a
report generator and loads them as querytables in the active worksheet. The
problem I am having is when I try to execute the QueryTables.Add method.

I have declared oXL as an object and instantiated it with

Set oXL = Application

When I execute this code...

With oXL.ActiveSheet.QueryTables.Add(connection:=gConnect,
Destination:=Range("A5"), sql:=sql$)
..Refresh
End With

I get an error message that says "Method '~' of Object '~' failed."

The same syntax works fine if I try this from VBA, but not within my VB6
Addin. Any ideas as to what I am doing wrong?

Thanks
 
Hi Jace,

I think you may be running into an unqualified reference error, but it's
hard to tell without seeing all the code (and that oh-so-helpful error
displayed by VB <g>). See if this works:

With oXL.ActiveSheet
With .QueryTables.Add(connection:=gConnect, _
Destination:=.Range("A5"), sql:=sql$)
.Refresh
End With
End With

Notice the dot in front of Range("A5") - this way, you're explicitly telling
Excel that the range lives on the active sheet.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]
 
Back
Top