P
PeacefulAnd4getful
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)?
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
(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)?
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