Query table name persists after deletion

[Glory]

I'm trying to write some code to make a querytable. The problem is that, after the first table is deleted, when the second table is added, the name of the second table has a "_1" appended to the end.

I've tried deleting all the names on the worksheet, but that doesn't help. Any ideas?

 

[Glory]

Bump.

 

[Glory]

Bump.

 

[chuckchuckit]

It is possible you may need to remove the data connection to the 1st Query when you are done with the query, before doing a next query.

Look at the Menu, Data, Connections. Each query might start a new one and you could end up with a ton of them.

Even more connections than the San Jose Sharks score goals...

 

[Glory]

What sort of data type would that be?

Dim As

Edit: Wait, isn't a connection just a string? How do I reference the actual connection object used by the query table, and why would there be a conflict between a table named "Table" and the connection of another table named "Table" that was already deleted?

 

[Glory]

sConn = QueryTables(1).Connection

But I need the object itself, if you're right about this. How do I refer to it?

 

[chuckchuckit]

Not sure what you mean by data type.

I do a lot of querying using query tables, and once I have the data from that query I have to delete the "connection" in the Menu, Data, Connection box.

I would probably try stepping through your code doing your 1st query, and after you are done with the data from the query, look at the Menu, Data, Connection box. The connections there will say Connection1, Connection2, Connection3 etc.

The one assigned to the query no longer used must be deleted, or you will end up with a bag full of used hockey pucks (subtle humor there).

 

[chuckchuckit]

I am assuming that every time you query you are creating a"new" connection.

And that may be where the duplicate names are being generated with the appended _1 perhaps. At least I would start looking there.

 

[chuckchuckit]

If you are using a query that has auto refreshing done every minute for instance, you would "not" want to delete the connection or the auto next query will not work. Because the connection is gone if you delete it.

But if you are doing a lot of single queries with no auto refresh being done then the connection needs to be deleted, because it no longer has any use.

If you only are doing one query at a time and no other queries you do will be auto refreshed then, you will only have one connection running at any one time. It will be named "Connection". If not deleted, any additional ones will be the Connection1, Connection2 etc.

Here is the line of code I use to delete just the one named "Connection"

ActiveWorkbook.Connections("Connection").Delete

It removes "only" the connection named "Connection" no longer needed.

 

[chuckchuckit]

If you are not doing any auto refresh querying, and you want to make sure "all connections" are deleted, then this following code will remove all connections no matter how many there are. It loops through and even if only 1 or 100 it will delete them:

Dim TheConnectionName As String
 
For Each objWBConnect In ThisWorkbook.Connections [COLOR=seagreen]'keeps looping until all connection names are found and removed (deleted)[/COLOR]
    TheConnectionName = objWBConnect.Name
    ActiveWorkbook.Connections(TheConnectionName).Delete [COLOR=seagreen]'removes connection[/COLOR]
Next objWBConnect