Downloading data from a website using Excel VBA

I have some old code that used to work but it doesn't now. My goal is to download data from a website. In this test case the URL is "https://flightaware.com/live/flight/FHPJA/history/1280?filterStart=2017-12-01&filterEnd=2017-12-31" What I want to download is the ACTIVITY LOG table data.

Right now I'm getting this error message:

'Run-time error '1004':
'Method 'Range' of object'_Global' failed

Here's the code. I'm wondering, among other things, if there's a reference in the VB Editor that I need to enable for the code to work.
Sub FlightawareGrab2()
With ActiveSheet.QueryTables.Add(Connection:= _
   "https://flightaware.com/live/flight/FHPJA/history/1280?filterStart=2017-12-01&filterEnd=2017-12-31", Destination:=Range([K3]))
   .Name = "FHPJA"
   .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 = xlWebFormattingNone
   .WebTables = "2"
   .WebPreFormattedTextToColumns = True
   .WebConsecutiveDelimitersAsOne = True
   .WebSingleBlockTextImport = False
   .WebDisableDateRecognition = False
   .WebDisableRedirections = False
   .Refresh BackgroundQuery:=False
End With

End Sub

Open in new window


Thanks!
John
LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Please give this a try and see if this works for you...

Sub FlightawareGrab2()
With ActiveSheet.QueryTables.Add(Connection:= _
   "URL;https://flightaware.com/live/flight/FHPJA/history/1280?filterStart=2017-12-01&filterEnd=2017-12-31", _
   Destination:=Range("K3"))
   .Name = "FHPJA"
   .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 = xlWebFormattingNone
   .WebTables = "2"
   .WebPreFormattedTextToColumns = True
   .WebConsecutiveDelimitersAsOne = True
   .WebSingleBlockTextImport = False
   .WebDisableDateRecognition = False
   .WebDisableRedirections = False
   .Refresh BackgroundQuery:=False
End With

End Sub

Open in new window

0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Awesome, Subodh!  It works perfectly with one small modification:
.WebTables = "3"

Open in new window


Thank you so much!

John
0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks again!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome John!
That's right, you were supposed to change the table index as per the data you want to fetch.
Glad your issue is resolved.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.