如何用Excel VBA发送带多参数及请求头的API POST请求？

我希望通过Excel和VBA向API发送带有参数和请求头的POST请求。

对应的单参数curl命令如下：

curl --location --request POST 'https://api-url-mycompany.net/countries' \
--header 'x-api-key: TEST01KEY' \
--form 'continent="europe"'

我已在VBA代码中成功传入API密钥，并实现了单个参数（如continent="europe"）的传递，以下是我的VBA代码：

Public Sub Area()

Dim XMLHTTP As New MSXML2.ServerXMLHTTP60
Dim myurl As String
Dim result As String
Dim Test() As String


myurl = "https://api-url-mycompany.net/countries"
XMLHTTP.Open "POST", myurl, False

XMLHTTP.setRequestHeader "x-api-key", "TEST01KEY"
XMLHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
XMLHTTP.send "continent=europe"

RESPONSE = req.responseText

Range("A1").Value = RESPONSE

End Sub

但我无法实现多参数的传递，假设对应的多参数curl命令如下：

curl --location --request POST 'https://api-url-mycompany.net/regions' \
--header 'x-api-key: TEST01KEY' \
--form 'continent="europe"' \
--form 'country="germany"'

恳请提供帮助！

情况1：API支持application/x-www-form-urlencoded格式传递参数 #

如果API接受URL编码的参数格式，直接用&拼接多个参数即可：

修改send方法的参数，完整代码示例：

Public Sub Area()
    Dim XMLHTTP As New MSXML2.ServerXMLHTTP60
    Dim myurl As String
    Dim response As String

    myurl = "https://api-url-mycompany.net/regions"
    XMLHTTP.Open "POST", myurl, False

    XMLHTTP.setRequestHeader "x-api-key", "TEST01KEY"
    XMLHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    ' 用&拼接多参数
    XMLHTTP.send "continent=europe&country=germany"

    response = XMLHTTP.responseText ' 修正原代码的变量错误（原用req未定义）
    Range("A1").Value = response
End Sub

情况2：API仅支持multipart/form-data格式（对应curl的--form参数） #

若API要求必须用表单数据格式传递，需手动构造multipart/form-data请求体：

Public Sub PostMultiPartForm()
    Dim XMLHTTP As New MSXML2.ServerXMLHTTP60
    Dim myurl As String
    Dim boundary As String
    Dim postData As String
    Dim response As String

    ' 自定义分隔符（避免与参数内容冲突即可）
    boundary = "----WebKitFormBoundary7MA4YWxkTrZu0gW"
    myurl = "https://api-url-mycompany.net/regions"

    XMLHTTP.Open "POST", myurl, False
    XMLHTTP.setRequestHeader "x-api-key", "TEST01KEY"
    ' 指定multipart/form-data格式及分隔符
    XMLHTTP.setRequestHeader "Content-Type", "multipart/form-data; boundary=" & boundary

    ' 构造表单数据内容
    postData = "--" & boundary & vbCrLf
    postData = postData & "Content-Disposition: form-data; name=""continent""" & vbCrLf & vbCrLf
    postData = postData & "europe" & vbCrLf
    postData = postData & "--" & boundary & vbCrLf
    postData = postData & "Content-Disposition: form-data; name=""country""" & vbCrLf & vbCrLf
    postData = postData & "germany" & vbCrLf
    ' 结尾分隔符需添加双横杠
    postData = postData & "--" & boundary & "--" & vbCrLf

    XMLHTTP.send postData

    response = XMLHTTP.responseText
    Range("A1").Value = response
End Sub

注意事项 #

• 原代码存在变量错误：使用req.responseText但未定义req，需改为XMLHTTP.responseText。
• 优先尝试第一种URL编码方式，若API返回错误再切换为multipart/form-data格式。

内容的提问来源于stack exchange，提问作者boathon