Selection.QueryTable problem

[Guest]

Hi,

I am trying to use the following in an excel (2003) spread sheet:

With Selection.QueryTable
..Connection= "ODBC;DSN=Live;UID=admin;;SERVER=LIVE;DBNAME=DATA;LUID=admin;"
.CommandText = strSelectStatement
.Refresh BackgroundQuery:=False
End With

and I keep getting :

Error Number: 1004
Error Description: Application-defined or object-defined error

I have had a quick look at the help files and online and have tried a number
of alternatives but with no success.

Any help/tips greatly appreciated.

Thanks,
J

 

[Sharad Naik]

Selection.QueryTable will give that error.
This is not proper way to refer to a QueryTable.
You can do, for example:

With ActiveSheet.QueryTables(1)
'if 1 is the correct index of the required querytable.
'else replace 1 with the correct index.

Sharad

 

[Rob van Gelder]

I've created a Query Editor Add-In for Excel - freely available on my
website.
One of the features is a right-click / Edit.

This is the code I use for returning a QueryTable from a Selection.

Private Function SelectedQueryTable() As QueryTable
Dim qtb As QueryTable, rng As Range, rngResult As Range

If Not ActiveSheet Is Nothing Then
On Error Resume Next
Set rng = Selection.Cells(1)
On Error GoTo 0
If Not rng Is Nothing Then
For Each qtb In ActiveSheet.QueryTables
On Error Resume Next
Set rngResult = qtb.ResultRange
On Error GoTo 0
If Not rngResult Is Nothing Then
If Not Intersect(rng, rngResult) Is Nothing Then
Set SelectedQueryTable = qtb
Exit For
End If
End If
Next
End If
End If
End Function