CODEWITHSUNDEEP
jsonvbaexcelcurlweb-scraping
Mark Toledo
Mark Toledo

Reputation: 101

Convert CMD cURL to Excel VBA code in order to fetch JSON data in an HTTPS site?

I need to get a JSON from this URL "https://localhost/appserver/portal/api/1.0/apps" and I can do that using CMD cURL.

curl 'https://<host>/appserver/portal/api/1.0/apps' 
-H 'Accept-Encoding: gzip, deflate, sdch, br' 
-H 'Accept-Language: en-US,en;q=0.8' 
-H 'Upgrade-Insecure-Requests: 1' 
-H 'User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36' 
-H 'Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8' 
-H 'Referer:     https://<host>/appserver/portal/login;jsessionid=A7DE3EB54B8E5151DA304D90DB48DF2E' 
-H 'Cookie: JSESSIONID=952E2B1F8E714BE302CA902469DB0781' 
-H 'Connection: keep-alive' 
-H 'Cache-Control: max-age=0' 
--compressed 
--insecure

I am trying to fetch the JSON using Excel VBA. What is the proper way fetch JSON data using VBA in an HTTPS site?

Upvotes: 1

Views: 1360

Answers (1)

omegastripes
omegastripes

Reputation: 12612

Try something like this:

With CreateObject("MSXML2.ServerXMLHTTP")
    .Open "GET", "https://<host>/appserver/portal/api/1.0/apps", False
    .SetRequestHeader "Accept", "application/json, text/javascript, */*; q=0.01"
    .SetRequestHeader "Accept-Encoding", "gzip, deflate, sdch, br"
    .SetRequestHeader "Accept-Language", "en-US,en;q=0.8"
    .SetRequestHeader "Upgrade-Insecure-Requests", "1"
    .SetRequestHeader "User-Agent", "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36"
    .SetRequestHeader "Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8"
    .SetRequestHeader "Referer", "https://<host>/appserver/portal/login;jsessionid=A7DE3EB54B8E5151DA304D90DB48DF2E"
    .SetRequestHeader "Cookie", "JSESSIONID=952E2B1F8E714BE302CA902469DB0781"
    .SetRequestHeader "Connection", "keep-alive"
    .SetRequestHeader "Cache-Control", "max-age=0"
    .Send
    sHeaders = .getAllResponseHeaders
    sContent = .responseText
End With

You may try MSXML2.ServerXMLHTTP or MSXML2.XMLHTTP with a particular website. Note that MSXML2.XMLHTTP will send saved cookies regardless you add the cookie header or not.

Upvotes: 1

Related Questions