Run A Macro Against a Table Until it gets to bottom
 

First of all apologies for the title not sure how to reference this.

I have a table which contains a username and id number. For each
username I have a sheet with the same name.

Here is a few records from the table Column A and Column B

UNP-1ce 2168950611
UNP-ali169 111667564008
UNP-Bailey 111686165248
UNP-Bala420 27498979167
UNP-Blade 24241061987

I I am running a web query for each person where the difference in the
URL is the id number

Here is the code of the macro I use

Sub Update_Player()

Dim PlayerId As String
Dim PlayerName As String

PlayerId = "11801902765"
PlayerName = "UNP-Maximus"

With
Worksheets(PlayerName).QueryTables.Add(Connection: ="URL;http://www.stupidhero.com/DFA/SigConfig.cfm?PlayerID="
& PlayerId & "" _
, Destination:=Sheets(PlayerName).Range("A2"))
.Name = PlayerName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "13"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

End Sub

In theory I could replicate this macro over and over changing the name
and code but this seems rather bad :eek:

Is there anyway that I can get the macro to look at the contents of the
sheet with the name and code in it run the macro against row 1 then row
2 until the end.

Hopefully this makes sense,

Thanks in advance

Rob


--
racer25
------------------------------------------------------------------------
racer25's Profile: http://www.excelforum.com/member.php...o&userid=13057
View this thread: http://www.excelforum.com/showthread...hreadid=482189

Run A Macro Against a Table Until it gets to bottom
 

Assume your table is in standard format: no blank lines in between lines and
has a blank row beneath, blank column to the right of the data range modified
sub below, my additions don't have the :


Sub Update_Player()


Dim ListRange as Range, CurrentRow as Range

Dim PlayerId As String
Dim PlayerName As String


' Next line finds your data table (substitute actual sheet name):
Set ListRange = Worksheets("SheetName").Range("A1").CurrentRegion

'Now loop through the list:
For Each CurrentRow in ListRange.Rows

' Get the player ID and name:
PlayerName = CurrentRow.Cells(1,1)
PlayerId = CurrentRow.Cells(1,2)

' Eliminate the lines where you assigned the name & id

' This part should stay the same:
With
Worksheets(PlayerName).QueryTables.Add(Connection: ="URL;http://www.stupidhero.com/DFA/SigConfig.cfm?PlayerID="
& PlayerId & "" _
, Destination:=Sheets(PlayerName).Range("A2"))
.Name = PlayerName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "13"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With


' Next line in list:
Next CurrentRow

End Sub


--
- K Dales


"racer25" wrote:


First of all apologies for the title not sure how to reference this.

I have a table which contains a username and id number. For each
username I have a sheet with the same name.

Here is a few records from the table Column A and Column B

UNP-1ce 2168950611
UNP-ali169 111667564008
UNP-Bailey 111686165248
UNP-Bala420 27498979167
UNP-Blade 24241061987

I I am running a web query for each person where the difference in the
URL is the id number

Here is the code of the macro I use

Sub Update_Player()

Dim PlayerId As String
Dim PlayerName As String

PlayerId = "11801902765"
PlayerName = "UNP-Maximus"

With
Worksheets(PlayerName).QueryTables.Add(Connection: ="URL;http://www.stupidhero.com/DFA/SigConfig.cfm?PlayerID="
& PlayerId & "" _
, Destination:=Sheets(PlayerName).Range("A2"))
.Name = PlayerName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "13"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

End Sub

In theory I could replicate this macro over and over changing the name
and code but this seems rather bad :eek:

Is there anyway that I can get the macro to look at the contents of the
sheet with the name and code in it run the macro against row 1 then row
2 until the end.

Hopefully this makes sense,

Thanks in advance

Rob


--
racer25
------------------------------------------------------------------------
racer25's Profile: http://www.excelforum.com/member.php...o&userid=13057
View this thread: http://www.excelforum.com/showthread...hreadid=482189

Run A Macro Against a Table Until it gets to bottom
 

Thank you very much appreciate that

Works a treat


--
racer25
------------------------------------------------------------------------
racer25's Profile: http://www.excelforum.com/member.php...o&userid=13057
View this thread: http://www.excelforum.com/showthread...hreadid=482189