QueryTables.Add... name changes

peacefullAnd4getfull · May 14th, 2007, 01:06 PM

I have a procedure that loops through a list of hyperlinks on one worksheet (wsSource) and calls an ImportWebData routine. The ImportWebData procedure uses QueryTable.Add to grab the data, place it in a 2nd worksheet (wsTarget) and name the range holding the import data as "ImportData" (using .Name). Following the query, the mother routine calls "ProcessImportData" and passes wsTarget and .range("ImportData")

After ProcessImportData, the mother procedure deletes the "ImportData" name to re-use it on the next query.

Therein lies the problem. Each iteration of ImportWebData names the range holding the imported data as "ImportData_1", "ImportData_2", etc. (even though the name "ImportData" no longer exists.)

Here is my question followed by the sample code: How can I force the web query to always name the imported data range as "ImportData" (without the appended numbers)?

Code:

Sub GetAllWebData()
    Dim wsT As Worksheet
    Dim wsS As Worksheet
    Dim hLink As Hyperlink
    Dim sWebAddr As String
    Const ImportRangeName As String = "ImportData"
    Const iTableNumber As Integer = 9

    Set wsT = Worksheets("TargetSheet")
    Set wsS = Worksheets("SourceSheet")

    For Each hLink In wsS.Hyperlinks
        sWebAddr = hLink.Address
        ImportWebData wsS, wsT, sWebAddr, iTableNumber, ImportRangeName
        ProcessImportData wsT
        Range(ImportRangeName).Delete
    Next hLink
End Sub

Sub ImportWebData(wsSource As Worksheet, wsTarget As Worksheet, _
        sWebAddr As String, iTableNumber As Integer, _
        ImportRangeName As String)
    Dim qTab As QueryTable

    Set qTab = wsTarget.QueryTables.Add(Connection:="URL;" & sWebAddr, _
            Destination:=wsTarget.Range("A1"))
    With qTab
        .Name = ImportRangeName
        .FieldNames = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingAll
        .WebTables = "11,12,13"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Application.qTab.Refresh BackgroundQuery:=False
End Sub

Sub ProcessImportData(ws As Worksheet)
'   statements to evaluate, manipulate and make decisions based upon
'   imported data located in ws.range(ImportRangeName)
End Sub

Thanks ahead of time for your help.