ODBC Query

[Alan]

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,

 

[FSt1]

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 refreshit()
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

 

[Alan]

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 refreshit()
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={AT
SQLplus};ADS=" & Server, _
Destination:=Config.Range("B1"))

Config is a worksheet and Get_Value is a named procedure: 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

 

[AB]

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").commandtext

 

[Alan]

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").commandtext
















- Show quoted text -

Thanks AB but the Config.QueryTables.Add line is only executed once
when the whole process is initialised so that is not a cause of the
issue ... or are you saying that once the query table exists in the
workbook it is not necessary to ADD again even if the workbook is
opened up and restarted from scratch? I use QTables so infrequently
that I can't honestly remember what I did last time.

I was trying to add the query table to a named range because I need to
use the returned data elsewhere and I don't like hard-coding cell
addresses into my code just in case the worksheet is later changed.
Perhaps I should create the QTable and then add the range name?

You have certainly given me a few pointers to experiment with.

Alan

 

[Alan]

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").commandtext
















- Show quoted text -

Gents,

Thanks for your assistance - you were both right, it just took a while
for the penny to drop.

Once a query table exists in a workbook, don't recreate it.
Add any named ranges to the area within a query table once the query
table has been created - not before!

Simple!!

Alan