Why is Excel appending numbers to querytables.

[Bing]

Hi,

Still haven't figured this one out.. hopefully someone knows why.

Programmatically i add a querytable (name=qt) to the querytables collection
of a worksheet:

ie.
With activesheet.QueryTables.Add(Connection:="TEXT;c:\log.txt"),
Destination:=Range("A2"))
.Name="qt"
End With

I then delete that querytable by: qt.delete.

But when i go to recreate that query table using the same name, "qt" for
some reason excel starts appending an underscore than a number to the query
table name each time i delete, then add that querytable with the same name
"qt". Ie. excel changes the name to "qt_1", or "qt_2", etc.

Anyone know why and how to stop this so taht i can use the same name "qt" to
reference the query table each time i add and delete it.

 

[Bing]

Sorry, but a typo..

i delete the querytable (name="qt") using the reference to the querytable
returned when i added it to the worksheet querytables collection, not via
qt.delete:

qtable = activesheet.querytables.add (... )
qtable.name = "grp"

qtable.delete

when i then add that table again:
qtable = activesheet.quertables.add(..)
qtable.name = "grp"

excel actually creates teh query table with name "grp_1", or "grp_2", not
"grp" as i specified.. Why?

 

[OJ]

Hi,
what version are you using because I cannot replicate this in 2002.
Works fine for me.

OJ

 

[Bing]

I'm using excel 2003.

 

[OJ]

Hi,
perhaps if you posted your entire routine(s) then I might be able to
help. My instincts ask me why delete and then recreate....can you not
just change the connection string and use the same table each time?

OJ

 

[Bing]

Hi OJ, Let me first say thanks for helping!

I suppose you could use same table, it just seemed cleaner to start from a
new querytable each time a new data file was loaded.. in any case here is the
code:

function parameters i used were:
qtName="grp"
qtConnection = "TEXT;" & commaDelimitedFilenameToImport
qtSht = any worksheet
qtCell = "A2"

Run this function a few times in the same VB session and inspect the
querytable name in the worksheet querytables collection each time you this is
function is called. I created a querytable name of "grp" I noticed that
eventho i thought i was deleting the "grp" query table, when i did a
qtSht.Querytables.count it was actually incrementing by 1 each time and the
names of each query table was "grp", "grp_1", "grp_2", etc.

Public Function InitQueryTable(qtName As String, qtConnection As String,
qtSht As Worksheet, qtCell As String) _
As QueryTable

On Error Resume Next
qtSht.QueryTables(qtName).Delete
Err.Clear

Dim qt As QueryTable
Set qt = qtSht.QueryTables.Add(Connection:=qtConnection, _
Destination:=qtSht.Range(qtCell))

With qt
.Name = qtName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
' .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1)
.TextFileTrailingMinusNumbers = True
.BackgroundQuery = False
End With
Set InitQueryTable = qt

 

[OJ]

Hi,
well...what happens when you remove the On Error Resume Next statement?
I would suggest that the Querytable is not getting deleted but rather
throwing an error. If I were you I would split this in to two
functions....one to create and one to destroy....

Public Function InitQueryTable(qtName As String, qtConnection As
String, _
qtSht As Worksheet, qtCell As String) As QueryTable

On error resume next
Set InitQueryTable = qtSht.QueryTables.Add(Connecti­on:=qtConnection,
_
Destination:=qtSht.Range(qtCel­l))
Let Err = 0

If Not initQueryTable is nothing then
With InitQueryTable
.Name = qtName
.FieldNames = True
......
.BackgroundQuery = False
End With
End If
End Function

'''This will return true if the table is found and then deleted...False
if there was no table to begin with

Public Function RemoveQTable(qtSht As Worksheet, strQTableName as
String) as Boolean
On Error Resume Next

qtSht.querytables(strQTableName).delete
If Err = 0 then RemoveQTable = True

End Function

Do you see what I'm trying to do here? It is usually best to write
functions to perform only one operation and then "sew" them together
with a sub routine....

Hth,
OJ

 

[Dick Kusleika]

Bing

I've had inconsistent results adding and deleting qts. When you create a
qt, Excel also creates a range name. Deleting the qt doesn't delete the
range name, however. If you create a qt with the same name as an existing
range name, Excel will append an incrementing digit to the end.

Based on that, you'd think you could just delete the named range when you
delete the qt, but that doesn't always work. I can't figure out what I'm
doing differently, because sometimes it does work.

The best way, in my opinion, is to not delete the qt in the first place. If
you want to change the qt, change the Connection and Commandtext properties
and Refresh it. Then you have the same qt object the whole time (with the
same name) and you just change certain aspects of it.

 

[Bing]

Hi Dick, OJ,

Thanks to both of you for trying to help out. Well i have just about given
up on this one too. Like you guys said, i'll just keep the old querytable
and reinit the connection parameters.

Thanks again for helping! Very much appreciated.

Cheers,