Load an image from URL with POST

Question

I need to load an image by calling an API and passing a JSON string via POST. The API returns a binary PNG image.

There are answers for loading an image from URL and sending a HTTP POST request, but not the two together.

How to get images to appear in Excel given image url

How can I send an HTTP POST request to a server from Excel using VBA?

update: Here is the code I have so far:


Sub LoadQR()

Dim d As Byte
JSON = Range("C5").Value
 

Set objHTTP = CreateObject("MSXML2.XMLHTTP")
Url = "https://xxxxxxx/xxxx/api/qr/v1/gen"
objHTTP.Open "POST", Url, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.setRequestHeader "Content-Type", "application/json"

objHTTP.send JSON
 
    ' Put response data into a file.
    Open "qr.png" For Binary As #1
    d = objHTTP.ResponseText
    Put #1, 1, d
    Close
    
    ' Load the data file as a picture.
    Set myDocument = Worksheets(1)
    myDocument.Shapes.AddPicture _
    "qr.png", _
    True, True, 500, 100, 70, 70
End Sub

Getting type mismatch because API response is actually binary, but objHTTP.ResponseText is a string.. any advice?

You could save the image from the POST response body, but it's unclear exactly what you want to do with it... — Tim Williams, Feb 27 '23 at 17:04
@CDP1802 , load the image anywhere in the sheet... just display it and be able to print it. It is actually a QR code — Ivan the Smurf, Feb 28 '23 at 11:18

score 0 · Accepted Answer · answered Feb 28 '23 at 13:44

Got it:

Sub LoadQR()

Dim d() As Byte
JSON = Range("C5").Value
 

Set objHTTP = CreateObject("MSXML2.XMLHTTP")
Url = "https://xxxxxx/api/qr/v1/gen"
objHTTP.Open "POST", Url, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.setRequestHeader "Content-Type", "application/json"

objHTTP.send JSON

    Open "qr.png" For Binary As #1
    d = objHTTP.ResponseBody
    Put #1, 1, d
    Close
    

    Set myDocument = Worksheets(1)
    myDocument.Shapes.AddPicture _
    "qr.png", _
    True, True, 450, 40, 200, 200

End Sub

Load an image from URL with POST

1 Answers1