Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Destination Range Names to be deleted.
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 -- Guru Vikram ------------------------------------------------------------------------ Guru Vikram's Profile: http://www.excelforum.com/member.php...o&userid=36404 View this thread: http://www.excelforum.com/showthread...hreadid=564171 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Destination Range Names to be deleted.
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 wrote: 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 -- Guru Vikram ------------------------------------------------------------------------ Guru Vikram's Profile: http://www.excelforum.com/member.php...o&userid=36404 View this thread: http://www.excelforum.com/showthread...hreadid=564171 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Destination Range Names to be deleted.
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 -- Guru Vikram ------------------------------------------------------------------------ Guru Vikram's Profile: http://www.excelforum.com/member.php...o&userid=36404 View this thread: http://www.excelforum.com/showthread...hreadid=564171 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Destination Range Names to be deleted.
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 wrote: 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 -- Guru Vikram ------------------------------------------------------------------------ Guru Vikram's Profile: http://www.excelforum.com/member.php...o&userid=36404 View this thread: http://www.excelforum.com/showthread...hreadid=564171 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Destination Range Names to be deleted.
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! -- Guru Vikram ------------------------------------------------------------------------ Guru Vikram's Profile: http://www.excelforum.com/member.php...o&userid=36404 View this thread: http://www.excelforum.com/showthread...hreadid=564171 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Destination Range Names to be deleted.
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 wrote: 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! -- Guru Vikram ------------------------------------------------------------------------ Guru Vikram's Profile: http://www.excelforum.com/member.php...o&userid=36404 View this thread: http://www.excelforum.com/showthread...hreadid=564171 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Destination Range Names to be deleted.
Thanks Duncan, Its working! Sorry, i didn't replied you in time, i was not in the town. Thanks once again! -- Guru Vikram ------------------------------------------------------------------------ Guru Vikram's Profile: http://www.excelforum.com/member.php...o&userid=36404 View this thread: http://www.excelforum.com/showthread...hreadid=564171 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste names from a source workbook to a destination | Excel Discussion (Misc queries) | |||
Destination range | Excel Discussion (Misc queries) | |||
Names not deleted until book closed | Excel Programming | |||
Some 'Names' cannot be deleted by Insert|Name|Define-Delete button | Excel Worksheet Functions | |||
Range names being deleted and altered | Excel Programming |