My goal is to obtain information from a url via the API, return the data as JSON, parsing it and then returning it to Excel range.
The problem I’m facing arises when the 1st url gets redirected automatically. I have tried to implement a wait in the code, to wait till the 1st url is redirected to the 2nd url that contains the information, but my code keeps on returning the information of the 1st url.
I have inserted a message box in the code to see which string is obtained.
Any help to get the string of the 2nd url, will be appreciated.
Public Sub ScrapeWebPage()
Dim URL As String
URL = "https://www.sanparks.org/includes/S...tionMonthList.php?resort=10&month=7&year=2022"
Set MyRequest = CreateObject("MSXML2.XMLHTTP.6.0")
With MyRequest
.Open "GET", URL, False
.send
End With
With MyRequest
While Not .ReadyState = 4
Application.Wait Now + TimeValue("0:00:01")
Wend
If .Status = 200 Then
While InStr(1, .responseText, "Updating", 0) > 0
Application.Wait Now + TimeValue("0:00:01")
Wend
End If
response = .responseText
End With
MsgBox response 'To see if it provides the string of the 2nd url
'json parsing and return to Excel range
End Sub
The problem I’m facing arises when the 1st url gets redirected automatically. I have tried to implement a wait in the code, to wait till the 1st url is redirected to the 2nd url that contains the information, but my code keeps on returning the information of the 1st url.
I have inserted a message box in the code to see which string is obtained.
Any help to get the string of the 2nd url, will be appreciated.
Public Sub ScrapeWebPage()
Dim URL As String
URL = "https://www.sanparks.org/includes/S...tionMonthList.php?resort=10&month=7&year=2022"
Set MyRequest = CreateObject("MSXML2.XMLHTTP.6.0")
With MyRequest
.Open "GET", URL, False
.send
End With
With MyRequest
While Not .ReadyState = 4
Application.Wait Now + TimeValue("0:00:01")
Wend
If .Status = 200 Then
While InStr(1, .responseText, "Updating", 0) > 0
Application.Wait Now + TimeValue("0:00:01")
Wend
End If
response = .responseText
End With
MsgBox response 'To see if it provides the string of the 2nd url
'json parsing and return to Excel range
End Sub