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,


"Dick Kusleika" wrote:

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.


--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Bing wrote:
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?


"Bing" wrote:

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:\l og.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.