Adding new name to Names collection

[dusty]

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

 

[norie]

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...

 

[Peter T]

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]

Peter,

That worked nicely, thank you.

Regards,

Clive