# Excel Visual Basic Help Concatenation

**URL:** <https://boards.straightdope.com/t/excel-visual-basic-help-concatenation/697295>\
**Category:** Factual Questions\
**Created:** [September 2, 2014, 9:24pm UTC](https://boards.straightdope.com/t/excel-visual-basic-help-concatenation/697295 "2014-09-02T21:24:38Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![notfrommensa](https://avatars.discourse-cdn.com/v4/letter/n/f14d63/32.png) [@notfrommensa](https://boards.straightdope.com/u/notfrommensa)\
**Post date:** [September 2, 2014, 9:24pm UTC](https://boards.straightdope.com/t/excel-visual-basic-help-concatenation/697295/1 "2014-09-02T21:24:38Z")

</div>

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

```auto

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.

---

<div class="post-metadata">

**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\
**Post date:** [September 2, 2014, 10:17pm UTC](https://boards.straightdope.com/t/excel-visual-basic-help-concatenation/697295/2 "2014-09-02T22:17:23Z")

</div>

Something like this:

```auto

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.

---

<div class="post-metadata">

**Author:** ![notfrommensa](https://avatars.discourse-cdn.com/v4/letter/n/f14d63/32.png) [@notfrommensa](https://boards.straightdope.com/u/notfrommensa)\
**Post date:** [September 2, 2014, 10:33pm UTC](https://boards.straightdope.com/t/excel-visual-basic-help-concatenation/697295/3 "2014-09-02T22:33:38Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\
**Post date:** [September 2, 2014, 11:55pm UTC](https://boards.straightdope.com/t/excel-visual-basic-help-concatenation/697295/4 "2014-09-02T23:55:37Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![notfrommensa](https://avatars.discourse-cdn.com/v4/letter/n/f14d63/32.png) [@notfrommensa](https://boards.straightdope.com/u/notfrommensa)\
**Post date:** [September 3, 2014, 12:33am UTC](https://boards.straightdope.com/t/excel-visual-basic-help-concatenation/697295/5 "2014-09-03T00:33:59Z")

</div>

> [@Mangetout](#):
>
> Oops - I missed the other line that references the numeric portion, but the principle is the same:
> 
> .Name = “EventResult.aspx?eventid=” & MyEvent

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

---

<div class="post-metadata">

**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\
**Post date:** [September 3, 2014, 7:18am UTC](https://boards.straightdope.com/t/excel-visual-basic-help-concatenation/697295/6 "2014-09-03T07:18:00Z")

</div>

> [@notfrommensa](#):
>
> I said I know enough to get me in trouble.😉

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).