在VBA中将图像处理为GET响应

Question

我想集成一个数据-矩阵-代码生成器到Excel。由于我的VBA技能有限，并且我在fastAPI服务上有一个基于python的生成器，因此我想调用API并插入返回的png文件。让我们假设它是这个api。

我写了这个小的web请求，但是现在我陷入了如何使用响应并将其放入一个变量中，然后我可以在单元格中显示

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

Answer 1

试试这个:

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

Answer 2

您可以将url结果直接插入单元格，首先将url分配给变量，然后插入带有变量变量的图片。

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