Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding new name to Names collection
G'Day,
I've noticed that importing a text file via a macro (using .QueryTables.Add) also adds a new defined range name. I'd like to delete the new name after it is added to stop an accumulation of range names. I haven't noticed any pattern to where the new name is added to the Names collection - in one test case, with 24 current names, the new name was added at position number 7. Can anyone explain how the new names are added to the Names collection so they can be deleted easily? I'm not too sure about the naming of the added defined name either. Thanks, Clive |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding new name to Names collection
Clive
I think the name depends on what you are actually importing. If you are doing this in code you could actually miss out the Name part and you'll get something like ExternalData... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding new name to Names collection
Assuming the defined Name is same as the Query name -
Sub test() Dim sPrefix As String Dim ws As Worksheet Dim qt As QueryTable Set ws = ActiveSheet On Error Resume Next sPrefix = ws.Names(1).Name sPrefix = Left$(sPrefix, InStr(1, sPrefix, "!")) On Error GoTo 0 For Each qt In ws.QueryTables If Len(sPrefix) Then sName = Replace(qt.Name, " ", "_") ws.Names(sPrefix & sName).Delete End If ' qt.Delete ' delete the query too? Next End Sub Regards, Peter T "dusty" wrote in message ... G'Day, I've noticed that importing a text file via a macro (using .QueryTables.Add) also adds a new defined range name. I'd like to delete the new name after it is added to stop an accumulation of range names. I haven't noticed any pattern to where the new name is added to the Names collection - in one test case, with 24 current names, the new name was added at position number 7. Can anyone explain how the new names are added to the Names collection so they can be deleted easily? I'm not too sure about the naming of the added defined name either. Thanks, Clive |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding new name to Names collection
Peter,
That worked nicely, thank you. Regards, Clive |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Memory leak in names collection? | Excel Programming | |||
adding a VBA name to workbook names collection | Excel Programming | |||
Adding Range to Worksheet Names Collection | Excel Programming | |||
Names Collection | Excel Programming |