Website Extract to Excel using VBA

[rult1985]

Hi everyone - my first post so please be kind

I have a list of URLs in column A I need to get the data from these URL's into Column B it is JSON data but I can work with that once it's in Excel
There are 10000 URLs

I have used this and it works well for 1 URL that's in the code, but I need it to reference Column A (it's just a number change 1.json, 2.json etc)



Private Sub HTML_VBA_Excel()
Dim oXMLHTTP As Object
Dim sPageHTML As String
Dim sURL As String

'Change the URL before executing the code
sURL = "https://ipfs.io/ipfs/QmTn4e8DAhViozojav8AAwXiU2CwV7qM62dMYxdj8DcH8r/1.json"

'Extract data from website to Excel using VBA
Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
oXMLHTTP.Open "GET", sURL, False
oXMLHTTP.send
sPageHTML = oXMLHTTP.responseText

'Get webpage data into Excel
ThisWorkbook.Sheets(1).Cells(1, 1) = sPageHTML

MsgBox "XMLHTML Fetch Completed"

End Sub


Any help would be much appreciated.

 

[Dan_W]

Hi - this is easily accomplished through using a loop. Just one quick question - do you want a 'brake' in the code or something? It's just the idea of executing 10,000 internet calls one after the other worries me, because it can sometimes be hard to stop. Also, alot of webservices have rate limiting restrictions - meaning that you can't access their files, etc too many time in a certain period. I wonder if the code above might be a bit too fast? Do you know if there are any limitations you need to be conscious of?

 

[rult1985]

Hi Dan, thanks for the reply, I don't believe there are any restrictions. Are you able to explain the loop? or even better show me the code I would need?

Thanks

 

[Dan_W]

Sure. See below. I created a loop using the i variable - you can see that it goes from 1 to 10000. I then use that i variable to change the URL you're accessing, and also the row number that the JSON is being output to. It's a good idea not to hit the server with 10000 requests in quick succession, so I have also added some code to that. I wrote a PAUSE routine at the bottom - as it currently stands, this code will pause for 1 second between each call to the server. That's probably a bit long, so you may want to change this line to PAUSE 0.1, for example.

Hope this makes sense, but let me know if not.

Private Sub HTML_VBA_Excel()
Dim oXMLHTTP As Object
Dim sPageHTML As String
Dim sURL As String

Dim i As Long
Const WAIT As Boolean = True

For i = 1 To 10000
    'Change the URL before executing the code
    sURL = "https://ipfs.io/ipfs/QmTn4e8DAhViozojav8AAwXiU2CwV7qM62dMYxdj8DcH8r/" & i & ".json"
   
    'Extract data from website to Excel using VBA
    Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    oXMLHTTP.Open "GET", sURL, False
    oXMLHTTP.send
    sPageHTML = oXMLHTTP.responseText
   
    'Get webpage data into Excel
    ThisWorkbook.Sheets(1).Cells(i, 1) = sPageHTML

    If WAIT = True Then
        PAUSE 1
    End If
Next


MsgBox "XMLHTML Fetch Completed"

End Sub
Sub PAUSE(Period As Double)
    Dim t As Single
    t = Timer
    Do Until Timer - t > Period
        DoEvents
    Loop
End Sub

 

[Dan_W]

Sorry - there was a typo, and have just corrected it. Code above should work.

 

[rult1985]

Its running - Dan that is brilliant - Thank you so much

 

[rult1985]

Sorry Dan I am getting a run time error 2147012894 (80072ee2)
Operation timed out

Debug highlights - oXMLHTTP.send

Do you think this could be the limitations you mentioned?

 

[Dan_W]

Maybe, yes.

 

[Dan_W]

Did you have the WAIT setting enabled? How long was it pausing for?

 

[rult1985]

I left it as you had it at 1