Plaintext API call

[butch3]

Hi all. I'm new to VBA. I'm trying to pull text data in aggregate into a cell in excel for each zip code query. I'm sure there are other way to put the data in columns and ways to make the spreadsheet elegant. I just want the data transferred from the site to my spreadsheet and I can go and use tools in excel to get the info I need.

I'm not even sure I have the correct references set up for the .open or the .send. I also don't think I need Javascript, ActiveX, etc to retrieve the data since the api is providing the data in plaintext.

I know that I am going to have a problem with retrieving the zip codes from 00001 to 09999 because the leftmost to the adjoining rightmost zeros will be truncated.

See my miserable attempt at some code below.

Thoughts?


Sub Test()
Dim objHTTP As Object
Dim MyScript As Object


Set objHTTP = CreateObject("MSXML2.XMLHTTP")

For zip = 0 To 99999
Url = "https://godaven.bitbean.com/api/shuls/search-all?nusach=ari&query=" & zip

objHTTP.Open "GET", Url, False
objHTTP.send
Set MyScript = CreateObject("MSScriptControl.ScriptControl")
MyScript.Language = "JScript"

Set RetVal = MyScript.Eval("(" + objHTTP.responsetext + ")")
Sheets(1).Cells(zip, 1).Value = RetVal.USD

Next

End Sub

 

[Rijnsent]

Hi Butch,

that looks like a good start. What you could do: I've got a project connecting to APIs of crypto exchanges, you could basically take my code and work from there.
This is the code you'd use: https://github.com/krijnsent/crypto_vba/blob/master/ModWeb.bas
It will like your API always give back JSON. If you want to process that, check out the JSONconverter module in that same project. My code is based on a simpler version: http://www.808.dk/?code-simplewinhttprequest

Hope that helps,

Koen

 

[butch3]

Hi. I updated the code. However, I get an object definition error. I also think my code can be improved.

I don't think I need JSON because the text I'm trying to capture is plaintext. See this link for an example.

In terms of my code, I've updated it, but since I can't figure out how to pull the data and display it in the cell, I'll just work on the code to retrieve the first 10000 zips.

Sub Test()
Dim objHTTP As Object
Dim Zip As Long


Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")


For Zip = 0 To 99999
Url = "https://godaven.bitbean.com/api/shuls/search-all?nusach=ari&query=" & Zip


objHTTP.Open "GET", Url, False
objHTTP.send


Sheets(1).Cells(Zip, 1).Value = objHTTP.ResponseText


Next


End Sub

 

[Rijnsent]

Hi Butch,
your example link gives me back JSON.

{"total":4,"num_of_pages":1,"shuls":[{"id":1499,"name":"bais bezalel chabad","address":"8850 w. pico blvd.","city":"los angeles","state":"ca","zip":"90035","country":"usa","location_point":{"type":"Point","coordinates":[-118.385874,34.055121]},"status":"imported","shul_nusach":"ari","start_date":null,"end_date":null,"temp_shul_confirmed":false,"rabbi":"rabbi moshe levin ","phone":"(310) 385-2222","phoneExt":null},{"id":6491,"name":"chabad - the beverlywood shul","address":"1952 s. robertson blvd","city":"los angeles","state":"ca","zip":"90035","country":"usa","location_point":{"type":"Point","coordinates":[-118.386311,34.04571]},"status":"imported","shul_nusach":"ari","start_date":null,"end_date":null,"temp_shul_confirmed":false,"rabbi":"dov newman","phone":"3108366770","phoneExt":null},{"id":8041,"name":"beth menachem west la","address":"9126 w. pico blvd.","city":"los angeles","state":"ca","zip":"90035","country":"usa","location_point":{"type":"Point","coordinates":[-118.3908162,34.0552368]},"status":"imported","shul_nusach":"ari","start_date":null,"end_date":null,"temp_shul_confirmed":false,"phone":"6318809516","phoneExt":null},{"id":8235,"name":"chabad of sola (south la cienega)","address":"1627 s la cienega boulevard","city":"los angeles","state":"ca","zip":"90035","country":"usa","location_point":{"type":"Point","coordinates":[-118.376494,34.048204]},"status":"imported","shul_nusach":"ari","start_date":null,"end_date":null,"temp_shul_confirmed":false,"rabbi":"avraham zajac","phone":"4242884633","phoneExt":null}]}

Is there a specific part of that JSON (text) you're looking for?
Secondly: what is the exact error message you get and at what line do you get it? (You can step through your code with F8, line by line.)
Thirdly: your last line has a small issue: Sheets(1).Cells(Zip, 1).Value -> this will crash with value 0 (your first run of the loop), do Sheets(1).Cells(Zip+1, 1).Value to avoid.
Finally: please start testing with max 10 codes, that's plenty for testing. 10k lines will take a long time/server load.

Cheers,

Koen

 

[butch3]

I made some modifications to the code and tested it and it worked. I wrote some code for the first 10,000 zip codes but extracting 100,000 zip codes from the server causes excel to be in not responding mode. How can I overcome this issue. Can I pause the program every 100 until I click on y or n. Even if that would take forever, at least it wouldn't crash...

 

[Rijnsent]

Hi Butch,
that's excel, it can't really do that amount of scrape actions. What you could do is add this:

If Zip Mod 100 = 0 Then
    MsgBox "you are now at Zip " & Zip, vbOKOnly
End If

Even with that code, Excel will probably run into memory issues at some point, so don't forget to save your result on the way. That code should stop the code every 100 lines and give you the option to press OK.
Hope that helps,
Koen