VBA Excel extracting data from website with changing date in URL

Question

I am extremely new to VBA so any help is appreciated. I am trying to extract data from this website https://www.census.gov/construction/bps/txt/tb3u201601.txt. The 201601 in the url represents jan 2016. I would like to create a program that cycles through all the months until 2003 and puts all the data in an excel spreadsheet. So far I have written something that isolated the date (below) but I cannot figure out how to have it loop through the dates I need. Thanks again.

Sub Macro2()
'
' Macro2 Macro
'

'
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim str As String

str1 = "URL;https://www.census.gov/construction/bps/txt/tb3u"
str2 = "201601"
str3 = ".txt"
str = str1 & str2 & str3

With ActiveSheet.QueryTables.Add(Connection:= _
    str, Destination _
    :=Range("$A$2"))
    .Name = "tb3u201601_4"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

End Sub

Answer 1

Add a Loop at the parent

Sub Macro1()
    Dim startDate As Date
    Dim thisDate As Date
    Dim endDate As Date
    Dim string2 as String

    startDate = DateSerial(2003, 1, 1)
    endDate = DateSerial(2016, 4, 1)
    Dim i As Integer

    Do
        thisDate = DateAdd("m", i, startDate)
        string2 = Format(thisDate,"yyyyMM")
        Call Macro2 (string2)
        i = i + 1

    Loop While (thisDate <= endDate)

End Sub

Change Macro2 to accept string parameter

Sub Macro2(str2 as string)
....

and remove this line in Macro2

str2 = "201601"

Answer 2

You'll need a couple of nested loops, one for the months, and one for the years. You can dimension each part as an Int, and then call cStr() on each of them to covert them back to strings and combine them together.

Dim iYear, iMonth as Int

For iYear = 2003 to 2015
    For iMonth = 1 to 12
        str2 = cStr(iYear) & cStr(iMonth)
        'The rest of your code here...
    Next iMonth
Next iYear

You'll likely also need to find some way to dynamically change where you're starting each table. If the tables are the same number of rows, you can replace your Range($A$2) reference with something like Range(Cells(iRow, 1).Address), and add to iRow every time you go through the loop. (iRow = iRow + [the number of rows in the table]