Can't obtain url string after redirect

Nelj

New Member
Joined
Mar 30, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Forum statistics

Threads
1,223,627
Messages
6,173,420
Members
452,514
Latest member
cjkelly15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top