Destination Range Names to be deleted.

[Guru Vikram]

Hi,
First of all I, would like to thanks this Excel Forum Team. Recently I
have joined this Forum. It is absolutely magestic. It helped me a lot.
With the help of this forum I've created a web query table. Now the
problem is, whenever I refresh the query, it generates the query name
automatically like : "ExternalData_1", after next refreshment -
"ExternalData_2" & so on. Till this time it has been created almost 183
names ("ExternalData_183"). Manually I've deleted all the names by
following steps :
From Menu - Insert > Name > select name Delete.
Now i wish to delete the previous name or overwrite the previous name,
whenever I, refresh the query table. I've checked lots of threads in
this regard & tried accordingly, but it did'nt worked for me. For the
ready reference, here is the codes :
Private Sub GetWebData()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim strName As String
Dim strNewEntry As String
Dim qtbQTb As QueryTable

Set ws1 = ThisWorkbook.Worksheets("Summery")
Set ws2 = ThisWorkbook.Worksheets("WebPage")
Set ws3 = ThisWorkbook.Worksheets("Dbase")

With ws2
If .QueryTables.Count <> 0 Then
For Each qtbQTb In .QueryTables
If qtbQTb.Name = strName Then
On Error Resume Next
ws2.Range("A1:L184").ClearContents
On Error Resume Next
qtbQTb.Delete
On Error Resume Next
..Names(strName).Delete
On Error GoTo 0
End If
Next qtbQTb
End If
End With
With ws2.QueryTables.Add(Connection:= _
"URL;http://mvc.serviesin.com, Destination:=ws2.Range( _
"A1"))
..Name = strName
..FieldNames = True
..RowNumbers = False
..FillAdjacentFormulas = True
..PreserveFormatting = True
..RefreshOnFileOpen = False
..BackgroundQuery = True
..RefreshStyle = xlOverwriteCells
..SavePassword = True
..SaveData = False
..AdjustColumnWidth = True
..RefreshPeriod = 0
'.WebSelectionType = xlEntirePage
'.WebFormatting = xlWebFormattingNone
'.WebPreFormattedTextToColumns = True
'.WebConsecutiveDelimitersAsOne = True
'.WebSingleBlockTextImport = False
'.WebDisableDateRecognition = False
'.WebDisableRedirections = False
..Refresh BackgroundQuery:=False
End With
End sub

Any help in this regard will be highly appreciated.

Gur Vikram

 

[Duncan]

Guru,

If all you want to do is remove all the named ranges (to clear them) so
that you can add again from fresh..

try this code which is written by Tom Ogilvy

Sub DeleteNames()


Dim intNames As Integer, i As Integer
intNames = ThisWorkbook.Names.Count
For i = 1 To intNames Step 1
ThisWorkbook.Names(1).Delete
Next i
End Sub


Works for me!


Duncan

 

[Guru Vikram]

Duncan,
Thanks for your prompt response.
But in this case, I've named some ranges other than this query, which
has linked with the query table (say adjacent cell formulas). I am
affraid, with this code all the names may be deleted.
Can I try these codes?

Guru Vikram

 

[Duncan]

No,

This will delete all of the names, if you have a static list of the
names that you DONT want deleted then this can be built in........

Duncan

 

[Guru Vikram]

Any Clue!
like : if qtbQTB1 = "ExternalData*" then
..names (strName).delete
or something else.
I know, I'm very much nearer to the goal, but confused!
Desperately need help!

 

[Duncan]

Guru,

You will have to replace "Name1" and so on with the names of the ranges
you wish to keep, and you can add more 'Or's' if you have more names
than I put (5). Other than that, here you go

Dim NME As Name
For Each NME In ThisWorkbook.Names
If NME.Name Like "Name1" Or NME Like "Name2" Or NME.Name Like "Name3"
_
Or NME.Name Like "Name4" Or NME.Name Like "Name5" Then
'do nothing
Else
NME.Delete
End If
Next NME

Test, Test and Test again.........Post back and let me know how you got
on.

Duncan

 

[Guru Vikram]

Thanks Duncan,
Its working!
Sorry, i didn't replied you in time, i was not in the town.
Thanks once again!