Delete Named Ranges and reset Excel's memory of previous ranges

[steff.sullivan]

I'm trying to build a workbook that builds a query table based on a user's entry of a parameter and have this change when they enter a new parameter.

I've built the macro so that it's structured like so
1) clear any existing data
2) get data from report
3) copy and transpose it to another sheet
4) clear original site of the report

and attached this to a button called "Go".

That works fine, but Excel is remembering the fact that I've used the query table name before and incrementing it's name with a "_n" and is consequently causing me difficulties when I try referring to the named query table.

The code I'm using to delete existing named ranges is:
For Each nName In Names
nName.Delete
Next nName

Does anyone know how to do this and ensure that when excel creates the new named range it resets the count of the name to 0?

Thanks in advance

 

[Andrew Poulsom]

You could delete all the existing query tables on the worksheet. Then the new table will be QueryTables(1). Or you could assign the query table to an object variable when you create it and use that variable to reference it afterwards.

 

[steff.sullivan]

I delete all queries before deleting the names. Both need deleting as just deleting the query does not delete the name.

So the vba looks like:

For Each objConn In wb.Connections
objConn.Delete
Next objConn
For Each nName In Names
nName.Delete
Next nName

I have a static name property for my query:

                        Set qt = ws.QueryTables.Add(Connection:=ConnectString, Destination:=ws.Range("e4"))
                        With qt
                                .Name = "Details2914"

But this name is not the full name used by excel at the point of implementation when this is not the first time the macro has been used.

Even though the queries and the named ranges have been deleted, excel still remembers that they previously existed and names new queries accordingly.

I considered using a rand() to append a number to the name but as it is not in a loop and the macro would be kicked off discretely each time it's needed, there is the risk of the number being re-used, so I'd be facing the same problem with a rand() albeit less often. This is not a good solution IMO.

 

[steff.sullivan]

*bump*

 

[Andrew Poulsom]

If you are renaming the query what's the problem?

 

[steff.sullivan]

I'm not renaming the query- I'm dropping it and recreating it based on a new query string with a parameter input by a user. They should be able to re-use the sheet to enter a parameter, print off the sheet and then start again with a new parameter.

I was looking for an elegant solution but I may just have to work out the range the imported data spans and copy that instead of using the neat short hand.

It seems like a failing to me that Excel would remember the names of connections long since dead and buried.

 

[Andrew Poulsom]

I still don't really see the problem, but is deleting Connections the same as deleting QueryTables?

 

[Forest Gremlin]

I had a very similar issue. I had a DB I was pulling in and needed a reference area to compile the information in another sheet. The only way I have found to get around the stored names issue is to use extra names, and then cancel the query after finishing.

First name a single cell on the spreadsheet with a throwaway name for the QT.
example A1 as MyQT
A side note: I just keep adding names to the cell for all the different QTs I wish to put in that workbook. Currently it has six names attached to it.
Then start by clearing the old data from previous connections. (assuming you wish to reuse the same area for the new information)

If Range("MyQTstart").Value = "(QT_1st_Column_Header)" Then
  On Error Resume Next
  Range("QTName").ClearContents
  Names("QTName").Delete
Else
End If

Next you need to give your QT the same name as the Named Cell

Set QueryTable = ActiveSheet.QueryTables.Add( _
        Connection:=rs, _
        Destination:=Range("MyQTstart"))
With QueryTable
    .Name = "MyQT"

Of course this will stick it with the _1, however this allows it to be cleared out unlike the initial name.

Next to be safe I convert the QT to cell values, and then name the range of cells to the name I really want to call the Table.

Range("MyQTstart", Range("MyQTstart").Offset(Range("MyQT_1").Rows.Count - 1, Range("MyQT_1").Columns.Count - 1)).Value = Range("MyQTstart", Range("MyQTstart").Offset(Range("MyQT_1").Rows.Count - 1, Range("MyQT_1").Columns.Count - 1)).Value
Range("MyQTstart", Range("MyQTstart").Offset(Range("MyQT_1").Rows.Count - 1, Range("MyQT_1").Columns.Count - 1)).Name = "QTName"
 
QueryTables("MyQT_1").Delete
 
rs.Close
Set rs = Nothing
cn.Cancel  
cn.Close
Set cn = Nothing

The cancel then close seems to be the part that makes this work, although I am not fully sure.