Selection.QueryTable problem

[JenC]

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
"JenC" <[email protected]> wrote in message
news:[email protected]...
> 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

[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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"JenC" <[email protected]> wrote in message
news:[email protected]...
> 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