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]

Jace wrote:
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:=gConne ct,
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