QueryTable deletion

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi all,

I've finally decided that its about time I became more familiar with using Excel to extract information from the Web. Specifically, I'm looking to get some P/E ratios for various categories of shares.

OK, I'm fine with actually importing the data. And I'm fine with refreshing the query. What I have had problems with is if I decide to delete an existing query and then use the same name for a new query.

The code I am using at the moment is as follows:

Code:
Sub Test_Bank()
    Const strMain As String = "URL;http://www.telegraph.co.uk/money/main.jhtml;"
    Dim strSessionID As String, strMenuID As String, strMItemID As String, _
        strView As String, strTRule As String, strGrid As String, _
        strSearch As String, strURL As String
    Const strQTName As String = "Banks"
    Dim qt As QueryTable
    
    On Error Resume Next
    With Sheet5
        For Each qt In .QueryTables
            If qt.Name = strQTName Then
                .Names(strQTName).RefersToRange.Clear
                .Names(strQTName).Delete
                qt.Delete
            End If
        Next qt
    End With
    On Error GoTo 0
    'remove old
    
    strSessionID = "sessionid=B2IF4CIIALBBHQFIQMFSM54AVCBQ0JVC?"
    strMenuID = "menuId=243"
    strMItemID = "menuItemId=2841"
    strView = "view=SHARERESULTS"
    strTRule = "targetRule=5"
    strGrid = "grid=M2"
    strSearch = "search=sectors/banks"
    
    strURL = strMain & strSessionID & strMenuID & "&" & strMItemID & _
        "&" & strView & "&" & strTRule & "&" & strGrid & "&" & strSearch
    
    Call NewQT(strURL, strQTName, Sheet5.Range("A1"))
    
End Sub

Sub NewQT(strURL As String, strName As String, rngDest As Range)
    
    With Sheet5.QueryTables.Add(Connection:=strURL, Destination:=rngDest)
        .Name = strName
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "12"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    
End Sub

The first time that the code is executed a QueryTable with the name "Banks" is created. The next time I run it I would have anticipated a new QueryTable with the same name. However, what I get is "Banks_1". If I run it a third time then there is no "Banks" to delete and I get both "Banks_1" and then new "Banks_2".

Anybody come across this before? Better yet, does anybody know how to do it correctly?
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
... I've also just noticed that if I run the code posted above once, then run the routine below, and then repeat the above I get the same problem.
Code:
Sub TestTheQTs()
    Dim qt As QueryTable

    With Sheet5
        For Each qt In .QueryTables
            If qt.Name = "Banks" Then
                .Range(qt.Name).Clear
                .Names(qt.Name).Delete
                qt.Delete
            End If
        Next qt
    End With
    
End Sub
But, if I Save and Close the workbook after running the above and then run the main routine when the workbook is re-opened ... it works! :unsure:
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Hi Richie,

You'll want to deleting Range and name that Excel creates when you create the new Query works for me.

You might be better off using index numbers (e.g., Querytables(1)).
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Nate,

Thanks for the input.

I am clearing the range, deleting the name, and deleting the QT before adding the new QT. Unfortunately this doesn't work for me (even using index numbers rather than names, which I'd prefer to avoid doing if possible).

In case its a Version issue, I'm using Windows XP Home (SP2) and Excel 2003.

Any further thoughts? (I'm beginning to think the best bet would be to create a new workbook to hold the QTs, copy the data across to the intended workbook, and then delete the new workbook)
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700

ADVERTISEMENT

Hi Richie,

You could try refreshing versus recreating.

But yeah, noticing a similar result with your code... Although the number doesn't seem to always increment for me; not consistent in my tests...

What's wrong with the index?

It's caching something somewhere... :confused:
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Nate,

Yeah, refreshing works fine. This is purely curiousity from a technical perspective as to why the issue outlined above happens when I try to recreate rather than refresh.

As regards use of the index, its nothing more than I find it more intuitive to use the name - its easier to keep track of.

Oh well, I'll keep testing ... ;)
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Why not use a public variable for the name that you can refer to and update.

I believe this is the same logic behind when you open a new workbook. For example, when you open a new workbook the name is always Book1. Close the book (but keep Excel open) and open a new book and this one is Book2. The same happens when you draw controls on a sheet - its CheckBox1 then CheckBox2 etc. If you delete one then do it again the next one is CheckBox3 etc. So obviously Excel stores the number to use next whenever an object is created. Closing the Application will reset this.
 

rik1234

New Member
Joined
Aug 3, 2005
Messages
1
I had the same problem, when deleting the old query table, try

for each qt in sheet.querytables
qt.resultrange.DELETE (instead of clear)
qt.delete
next qt

it worked for me :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,383
Messages
5,601,316
Members
414,441
Latest member
KellyTheKid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top