Plaintext API call

butch3

New Member
Joined
Feb 4, 2019
Messages
46
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,300
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 4, 2019
Messages
46
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

Well-known Member
Joined
Oct 17, 2005
Messages
1,300
Office Version
  1. 365
Platform
  1. Windows
Hi Butch,
your example link gives me back JSON.
Code:
{"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

New Member
Joined
Feb 4, 2019
Messages
46
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

Well-known Member
Joined
Oct 17, 2005
Messages
1,300
Office Version
  1. 365
Platform
  1. Windows
Hi Butch,
that's excel, it can't really do that amount of scrape actions. What you could do is add this:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,971
Messages
5,621,891
Members
415,864
Latest member
cybid

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
Top