I am new to VBA and have been facing a lot of trouble with figuring out how to pull data from the website boxofficemojo.com. I am trying to extract weekly data for the years 2010-2015. So i found a code that did something along the same lines and changed it to suit my needs. It is as follows
Sub Movies()
Dim nextRow As Integer, i As Integer
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
For i = 1 To 52
Application.StatusBar = "Processing Page " & i
nextRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.boxofficemojo.com/weekly/chart/?yr=2015&wk=&p=.htm" & i, _
Destination:=Range("A" & nextRow))
.Name = "weekly/chart/?yr=2015&wk=&p=.htm"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "5"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ThisWorkbook.Save
Next i
Application.StatusBar = False
End Sub
However instead of pulling the data for weeks 1 - 52 of 2015, it keeps on pulling data for the latest week of 2016 and repeats it 52 times. I am not sure what's wrong here and any help would be really really appreciated.
Thanks for your effort.
You are very close to being there.
Adjust the url string in the QueryTables.Add method to this:
http://www.boxofficemojo.com/weekly/chart/?yr=2015&wk=" & i & "&p=.htm"
Since you stated you desire 2010 to 2015. You can wrap the existing loop inside another loop For x = 2010 to 2015 and then modify the URL to this:
http://www.boxofficemojo.com/weekly/chart/?yr=" & x & "&wk=" & i & "&p=.htm"