I'm not sure i follow why you need the named ranges in the query
result range - perhaps pulling it by some formulas elsewhere..
Anyway - this is what makes the columns shift:
Config.QueryTables.Add
as every time it adds a new query table.
As FST1 noted - you don't have to insert it anew every time - you can
use the existing one and just modify its .commandtext property and
refresh.
You can refer to the query table as FST1 shows you or
Config.Querytables(1).commandtext
or
Config.Querytables("QueryNameGoesHere").commandtex t



On Jan 24, 1:09*pm, Alan wrote:
On Jan 22, 2:34*am, FSt1 wrote:





On Jan 21, 7:15*am, Alan wrote:

I am pulling data into Excel using ODBC. The returned data consists of
4 strings/numbers but, instead of being written to my defined
"results" range, the "system" is inserting 4 extra columns to the left
of my defined range for each and every iteration. Is there a way that
I can prevent this from happening such that the returned data
automatically gets written into the defined range?

Regards,

hi
you didn't really give enough info to tell what's really wrong.
posting your code would be very helpful.
but from what you did post, it sounds like you are trying to recreate
the query each time you run it. that is really not necessary. once the
query has been created, excel assigns a name to it, a named range and
gives it properties. after that all you need to do is refresh the
query table. and 1 line of code would do it.
assuming query table is on sheet1 at cell A1.
Sub refre****()
Sheets("sheet1").Range("A2").QueryTable.Refresh BackgroundQuery:=False
MsgBox "done"
End Sub

you could also right click the query table and from the popup, click
refresh.

so if i have it wrong, post the code you are using.

regards
FSt1

Hi,

Here is the guts of my query ... it is executed approximately 20 times
for each run.

Sub RunQuery(ByVal i As Integer, ByVal qDate As String)

* * Dim CmdText As String

* * qDate = Format(qDate, "dd-mmm-yy")
* * Application.DisplayAlerts = False

' Set the query command text
* * CmdText = _
* * * * "Get_Value('" & TagRefs.Cells(i, 1).Value & "', '" & qDate &
"')"

' Run the query
* * With QTable
* * * .CommandText = CmdText
* * * .Refresh BackgroundQuery:=False
* * End With

* * Application.DisplayAlerts = True

End Sub

Where the QTable is defined by:

Set QTable = Config.QueryTables.Add(Connection:="ODBC;Driver={A T
SQLplus};ADS=" & Server, _
* * * * Destination:=Config.Range("B1"))

Config is a worksheet and Get_Value is a named procedu the server
name is defined elsewhere. Originally the destination for the QTable
was a defined range but after much messing around I found that Excel
doesn't seem to like named ranges on the same row as a query table -
if such a range exists the returned query is returned into a new,
Excel generated, named range in inserted columns to the left of the
previous range. If no named range exists, Excel quite happily
overwrites the names that it generates for itself.

I now remove all named ranges from the query table rows before a new
query is run - this isn't what I had originally intended and it is a
bit of a pain but it does seem to work. Any advice would still be very
welcome.

Alan- Hide quoted text -

- Show quoted text -