Query Tables naming not going to plan...

[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

 

[Joel]

The Query names are named ranges you can look at by going to worksheet menu
Insert - Name - Define or from Files - properties - Contents


You can search the name ranges in a macro using the macro below

Sub getname()

Lookup = "PDEnglandWebQuery"

Set nms = ActiveWorkbook.Names
For r = 1 To nms.Count
If InStr(nms(r).Name, Lookup) > 0 Then

End If
Next

End Sub

 

[George]

Joel - thanks for the response although it doesn't quite answer my question.
Essentially I only want one query on the sheet (the one originally created
called PDEnglandWebQuery) and if the VB macro detects this query as being
present then it refreshes the query, rather than creating a new one.

Of course I could detect the presence of it by using Instr, but I was rathr
hoping for a more elegant solution, or a fix for the appending of the
underscored number. If Excel automatically adds this, and there's nothing I
can do about it, I guess I'll have kludge it but I'd rather get to a neater
solution if one is available

George

 

[George]

Just a quick further edition to this as well...as I have five different
sheets each with their own individual web query, I'd need to loop through
each name to find out if it exists for each sheet...this seems like an
unnecessary waste of steps to me - if I was able to ensure the name of the
QueryTable remained what I'd set it (PDAmericaWebQuery rather than ***_1)
then I could detect the presence of the QT rather than search for it each
time.

On this particular spreadsheet it's not a big issue as there are only five
QueryTables but if I had a few more then the looping through all QTs just to
check if it's present would be needless surely...

Anyway, I'm going to pop that code in for the moment so I can at least get
the refresh code into shape in preparation for a more elegant solution.
Thanks for the assistance Joel - it's always appreciated!

George

 

[Don Guillett]

I'm not quite sure of what you are doing but perhaps you could DELETE the
name of the existing external query and then your add works. OR, if a
continuing query, establish the query>record a macro while refreshing to see
what is happening and then always refresh... Just a couple of thoughts.

 

[ward376]

Have you tried testing for part of the qt name?

Sub qtRefresh()

Dim ws As Worksheet
Dim qt As QueryTable

For Each ws In Sheets(Array(Sheet1.Name, Sheet2.Name))
If ws.QueryTables.Count > 0 Then 'this may help also
For Each qt In ws.QueryTables
If LCase(Left(qt.Name, 9)) = LCase("PDEngland") Then
qt.Refresh BackgroundQuery:=False
Else
'create a query
End If
Next qt
End If
Next ws

End Sub

Cliff Edwards

 

[George]

Thanks for the reply Don, however my problem comes about from Excel appending
stuff to the name that I tell it to call the Query. I've been able to use
Joel's suggestion of looping through each name to find the query, then
refresh it...however there's still no resolution on why Excel is appending
this "_1" to my query name when I set it!

It's pretty late in the day for me in the UK (to still be at work anyway) so
I'll catch up with this tomorrow. I've kind of solved the problem, albeit in
a kludgey fashion, so hey-ho - chalk that one up to experience and move on

George

 

[George]

Ward - thanks for the response however it's pretty much the same as Joel's
answer of looping through each name and trying to find my partiular query. My
problem comes about because Excel insists on appending "_1" to my query name
so I can't access it directly...for instance I can't be sure that it won't be
"_2" - other than relying on my coding genius of course

As I replied to Don, I've used Joel's suggestion (and this one really) of
looking through each query table to find "mine" (with the "_1") and then just
refreshed that particular query. Not elegant by any stretch of the
imagination, but hey-ho...move on and get on with something else I say!

Thanks for all the response, consider this matter closed for the
moment...unless anyone has an answer for why Excel appends this rubbish on my
query name. Something peculiar to 2003 for instance?

George

 

[Don Guillett]

Look in insert>name>define and you will probably see more than one with the
same name.
I always create one and refresh that one but you can always delete the first
one and the name will be re-created when you add a new query.

 

[Arnaud]

I have exactly the same problem creating QueryTables dynamicaly.
First time the name is ok, next attemps will produce ***_1 or ***_2 if I
don't remove previous ones.
So the problem still the same if I manually remove Names and Connections.
It seems ike Excel keep the name in an other place.
I even tried that before adding my QueryTables :
For Each WSheet In ThisWorkbook.Worksheets
For Each QTable In WSheet.QueryTables
QTable.Delete
Next QTable
Next WSheet
For Each n In ThisWorkbook.Names
n.Delete
Next

I can't find out any solution.