Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.QueryTable problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.QueryTable problem
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.QueryTable problem
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selection problem | Excel Discussion (Misc queries) | |||
Problem of selection.delete | Excel Worksheet Functions | |||
Selection problem with recorder | Excel Discussion (Misc queries) | |||
Cannot Group that selection Problem | Excel Discussion (Misc queries) | |||
range selection problem | Excel Programming |