Excel Visual Basic Help Concatenation

Factual Questions
1

Disclaimer: I know just enough Visual Basic to get me in trouble!

Here is my issue: I am trying to download golf tournament scores from the internet to a excel spreadsheet. I have found that World Rankings website is best suited for my task.
To start my process, I recorded the following macro



Sub Macro2()
'
'
'
'
    ActiveWorkbook.Worksheets.Add
'
    *With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.owgr.com/en/Events/EventResult.aspx?eventid=5454", Destination _
        :=Range("$A$1"))*
'
        *.Name = "EventResult.aspx?eventid=5454"*
        
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

I want to download all the 2014 Tournaments and I know inset a loop

For Event = 5454 to 5653
…
code
…
Next Event

What I need to know is how to concatenate my loop counter EVENT into the lines that I have italicized.

2

Something like this:



Sub Macro2()
'
'
'
'
For **MyEvent** = 5454 to 5653
    ActiveWorkbook.Worksheets.Add
'
    *With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.owgr.com/en/Events/EventResult.aspx?eventid=" & MyEvent & ", Destination _
        :=Range("$A$1"))*
'
        *.Name = "EventResult.aspx?eventid=5454"*
        
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

Next **MyEvent**
End Sub

I changed the name of your loop variable, because ‘Event’ is a reserved word.

3

Thank you Mangetout, I think that will work. Simpler than I thought!

4

Oops - I missed the other line that references the numeric portion, but the principle is the same:

.Name = “EventResult.aspx?eventid=” & MyEvent

5

Yep I figured that out by myself. I said I know enough to get me in trouble.:wink:

6

That’s how it starts.

It ends with you being the only person who knows how to support the business-critical application they asked you to write.

(I kid, because it sounds like you’re doing this for fun, but if you’re ever asked to write VBA stuff for work, beware).