Process image as GET response in VBA-CodePudding

I want to integrate a Data-Matrix-Code generator to Excel. Since my VBA skills are limited, and I have a Python-based generator on a fastAPI service, I want to call the API and insert the returned png-file. Let us assume, that it is this api.

I wrote this little web request but now I am stucked in how to use the response and putting it into a variable that I can then display in a cell.

Sub GetDataMatrixCode()
' Address string to API
Dim sUrlToPictureGenerator As String
' Create the object that will make the webpage request.
Dim oRequest As New MSXML2.XMLHTTP60 

' API endpoint of website
sUrlToPictureGenerator = "https://barcode.tec-it.com/barcode.ashx"

' Request data from website
With oRequest
    ' Build GET request
    .Open "GET", sUrlToPictureGenerator & "?data=This is a Data Matrix by TEC-IT&code=DataMatrix&imagetype=Png", True
    ' Send request to API
    .send
    ' Get the webpage response data into a variable
    
    'Output image in cell
    'ActiveSheet.Pictures.Insert (oRequest.responseBody)
End With
End Sub

CodePudding user response：

Try this:

Dim url as string

url = "https://barcode.tec-it.com/barcode.ashx" & _
      "?data=This is a Data Matrix by TEC-IT&code=DataMatrix&imagetype=Png"

ActiveSheet.Pictures.Insert url

CodePudding user response：

You can insert the url results directly into the cell by first assigning the url to a variant and then inserting the picture with the variant variable.

Sub GetDataMatrixCode() 
     ' Address string to API 
     On Error Resume Next
     Dim sUrlToPictureGenerator As Variant  

     sUrlToPictureGenerator = "https://barcode.tec-it.com/barcode.ashx?data=This is a Data Matrix by TEC-IT&code=DataMatrix&imagetype=Png"

    'Output image in cell

    With ActiveSheet.Pictures.Insert (sUrlToPictureGenerator ) 
       .ShapeRange.LockAspectRatio = msoTrue
       .Top = r.Top
       .Left = r.Left
    End With
    
     On Error GoTo 0

End Sub