0

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/SANParksApp/API/v1/bookings/accommodation/getAvailabilityAccommodationMonthList.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 'This is only to see if it provides the string of the 2nd url

'json parsing and return to Excel range
End Sub
Nelj
  • 1
  • 2
  • It's really hard to understand what the problem is, are you saying that you make a request and you can't get past the CloudFlare page? https://stackoverflow.com/q/32425973/495455 – Jeremy Thompson Mar 30 '22 at 06:04
  • @JeremyThompson Yes, I am trying to get past the CloudFlare page. I am after the string of the landing page. – Nelj Mar 30 '22 at 11:44
  • That's the point of CloudFlare, to remove DDoS attempts. – Jeremy Thompson Mar 30 '22 at 12:21

0 Answers0