G
George
Hi everyone, I'm using Query Tables to grab some data from our corporate
intranet and it's all working very well apart from one minor problem...each
query gets "_x" added to the name, where x is a number (starting at one).
The code for the QueryTable is as follows...essentially I have code before
this to check if we already have a Query open on the sheet and it refreshes
it rather than creating a new one...
Set qt = ws.QueryTables.Add(connection, ws.Range("A1"))
With qt
.name = "PD" & tabName & "WebQuery"
.FieldNames = True
.PreserveFormatting = False
.RefreshStyle = xlOverwriteCells
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.Refresh BackgroundQuery:=True
End With
"tabName" is just something to keep a track of which sheet we are
on...values can be anything you want (think countries).
When I create a QueryTable on each sheet in this way I get (for example):
PDEnglandWebQuery_1
PDAmericaWebQuery_1
....and so on
My refresh criteria is looking for the name set in the qt
("PDEnglandWebQuery") but obviously doesn't find it because of the _1.
Any suggestions on what I'm doing wrong?
Thanks
George
intranet and it's all working very well apart from one minor problem...each
query gets "_x" added to the name, where x is a number (starting at one).
The code for the QueryTable is as follows...essentially I have code before
this to check if we already have a Query open on the sheet and it refreshes
it rather than creating a new one...
Set qt = ws.QueryTables.Add(connection, ws.Range("A1"))
With qt
.name = "PD" & tabName & "WebQuery"
.FieldNames = True
.PreserveFormatting = False
.RefreshStyle = xlOverwriteCells
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.Refresh BackgroundQuery:=True
End With
"tabName" is just something to keep a track of which sheet we are
on...values can be anything you want (think countries).
When I create a QueryTable on each sheet in this way I get (for example):
PDEnglandWebQuery_1
PDAmericaWebQuery_1
....and so on
My refresh criteria is looking for the name set in the qt
("PDEnglandWebQuery") but obviously doesn't find it because of the _1.
Any suggestions on what I'm doing wrong?
Thanks
George