How do we use restful APIs from Excel macros (vba)?

by

You can use the MSXML library within VBA. Then you can create an XMlHTTP request and do a GET or POST etc. Here’s a code sample below. It uses late binding i.e. no need to reference the library first:

Option Explicit

Sub Test_LateBinding()

    Dim objRequest As Object
    Dim strUrl As String
    Dim blnAsync As Boolean
    Dim strResponse As String

    Set objRequest = CreateObject("MSXML2.XMLHTTP")
    strUrl = "https://jsonplaceholder.typicode.com/posts/1"
    blnAsync = True

    With objRequest
        .Open "GET", strUrl, blnAsync
        .SetRequestHeader "Content-Type", "application/json"
        .Send
        'spin wheels whilst waiting for response
        While objRequest.readyState <> 4
            DoEvents
        Wend
        strResponse = .ResponseText
    End With

    Debug.Print strResponse

End Sub

I’m using this testing website – JSONPlaceholder – to call a RESTful API. This is the response:

enter image description here

Note that I found that calls to this website with this method fail if you a) make a synchronous request, or b) use http not https.

More Related Contents:

  1. Print function selecting wrong Type of Change option excel vba
  2. Format Cell Contents
  3. Excel formula checklist in employees
  4. How do I put double quotes in a string in vba?
  5. Excel VBA – How to add dynamic array formula
  6. How can I URL encode a string in Excel VBA?
  7. Compare double in VBA precision problem
  8. Row count on the Filtered data
  9. Excel VBA Macro: User Defined Type Not Defined
  10. VBA rounding problem
  11. VBA – Write all possible combinations of 4 columns of data [duplicate]
  12. Remove duplicates from array using VBA
  13. 2 ways for “ClearContents” on VBA Excel, but 1 work fine. Why? [duplicate]
  14. Excel 2013 VBA Clear All Filters macro
  15. How to automatically insert a blank row after a group of data
  16. Get File Path (ends with folder)
  17. Get length of array?
  18. Email a single attachment from folder of files each to a different person
  19. Bad data selection using VBScript , on date field values
  20. Export chart as image – with click of a button
  21. Unicode string literals in VBA
  22. Error13 in Excel VBA in data file
  23. How to get the path of current worksheet in VBA?
  24. Excel VBA to match and line up rows
  25. Change font color for a part of text in cell
  26. Create a new object using the text name of the class
  27. Excel cell from which a Function is called [duplicate]
  28. How to extract text within a string of text
  29. How to open a workbook specifying its path
  30. GET pictures from a url and then rename the picture

Leave a Comment