QueryTable deletion

[Richie(UK)]

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:

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?

 

[Richie(UK)]

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

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!

 

[NateO]

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)]

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]

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

 

[Richie(UK)]

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]

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]

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