Hey guys,
I am a bit of a rookie with VBA and need to import a bunch of data from approx 115 different webpages with a very predictably changing URL.
Website: http...seas/1897.html
where 1897 is year.
I've simply recorded a macro of me doing it manually and want to edit the macro so it loops through for all webpages from year 1897-2013. Apart from the year changing, the URL remains identical. Additionally I would like to insert 1898 below 1897 all on the same worksheet, so that the data is sorted from oldest to newest. How do I create the loop? Below is my current code... Thanks!
Sub Macro2()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http...seas/1898.html", Destination:=Worksheets("Sheet3").Range("$A$1") _
)
.Name = "1897"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.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
Bookmarks