Creating QueryTable from VB6

[Jace]

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

[Jake Marx]

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:=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