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

A brief look suggests this could be simpler. Don't need hyperlinks, just the
url

Sub getdata()'UNtested
For Each murl In Range("whatever")
MsgBox murl
With ActiveSheet.QueryTables.Add(Connection:="URL;" & murl, _
Destination:=ActiveSheet.Range("A1"))
.Name = ImportRangeName
.SaveData = True
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "11,12,13"
.Refresh BackgroundQuery:=False
End With

'copy the data desired to somewhere and get the next

Next murl

End Sub
--
Don Guillett
SalesAid Software

"PeacefulAnd4getful" wrote in message
...
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

Just add this line when you want to delete the range name. For some reason, Excel remembers the old names used and just starts counting. And if you don't name it, Excel will name it for you. So really no point to even naming the querytables.add each time. Luckily, it loads the most recent data into Item 1 object.

Names(ActiveSheet.QueryTables.Item(1).Name).Delete

Hope this helps if you haven't figure out a solution yet. Wish I could figure out how to stop it from keeping track of each name it uses or reset it each time.

On Monday, May 14, 2007 2:22 PM PeacefulAnd4getful wrote:

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


On Monday, May 14, 2007 4:03 PM Don Guillett wrote:

A brief look suggests this could be simpler. Don't need hyperlinks, just the
url

Sub getdata()'UNtested
For Each murl In Range("whatever")
MsgBox murl
With ActiveSheet.QueryTables.Add(Connection:="URL;" & murl, _
Destination:=ActiveSheet.Range("A1"))
.Name = ImportRangeName
.SaveData = True
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "11,12,13"
.Refresh BackgroundQuery:=False
End With

'copy the data desired to somewhere and get the next

Next murl

End Sub
--
Don Guillett
SalesAid Software

"PeacefulAnd4getful" wrote in message
...


Submitted via EggHeadCafe - Software Developer Portal of Choice
Make The WebClient Class follow redirects and get Target Url
http://www.eggheadcafe.com/tutorials...arget-url.aspx