VBA使用User-XMLHTTP发送请求的方法

俯首为牛 2021-11-17 Hoeking 手机阅读

VBA 使用User-XMLHTTP发送请求

一、背景

在日常使用Excel VBA中, 有时需要调用外部接口, 记录VBA中使用User-XMLHTTP发送请求的方法;

二、GET请求方法

2.1 GET请求示例1

Private Function GetRequest(url As String) As String
    Dim XMLHTTP As Object
    Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    XMLHTTP.Open "GET", url, False
    XMLHTTP.setRequestHeader "Content-Type", "text/xml"
    XMLHTTP.setRequestHeader " charset=utf-8"
    XMLHTTP.setRequestHeader "Accept", "application/json"
    XMLHTTP.Send
    GetRequest = XMLHTTP.ResponseText
    Set XMLHTTP = Nothing
End Function

2.2 GET请求示例2

Private Function GetRequest2(url As String) As String
    Dim XMLHTTP As Object
    Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    XMLHTTP.Open "GET", url, False
    XMLHTTP.setRequestHeader "Content-Type", "text/xml"
    XMLHTTP.setRequestHeader " charset=utf-8"
    XMLHTTP.setRequestHeader "Accept", "application/json"
    XMLHTTP.Send
    GetRequest2 = XMLHTTP.ResponseText
    Set XMLHTTP = Nothing
End Function

三、POST请求方法

3.1 POST请求示例

Public Function PostRequest(url As String, Param As String) As String
    Dim XMLHTTP As Object
    Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    XMLHTTP.Open "POST", url, False
    XMLHTTP.setRequestHeader "Content-Type", "application/json"
    XMLHTTP.setRequestHeader "Accept", "application/json"
    XMLHTTP.Send (Param)
    PostRequest = XMLHTTP.ResponseText
    Set XMLHTTP = Nothing
End Function

3.2 POST请求示例

Public Function PostRequest2(url As String, body As String) As String
    Dim XMLHTTP As Object
    Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    XMLHTTP.Open "POST", url, False
    XMLHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    XMLHTTP.setRequestHeader "charset", "UTF-8"
    XMLHTTP.Send (body)
    PostRequest2 = XMLHTTP.ResponseText
    Set XMLHTTP = Nothing
End Function

四、发送请求方法说明

4.1 添加请求头

使用以下方法添加请求头：

XMLHTTP.setRequestHeader "Content-Type", "application/json"
XMLHTTP.setRequestHeader "charset", "UTF-8"

4.2 同步和异步

  • XMLHTTP.Open "GET", url, False 中的 False 表示同步请求
  • True 表示异步请求

4.3 设置超时

XMLHTTP.setTimeouts resolveTimeout:=60000, connectTimeout:=60000, sendTimeout:=60000, receiveTimeout:=60000

五、测试代码

Sub TestHttpRequest()
    Dim url As String
    url = "https://api.example.com/data"

    ' GET请求
    Dim result As String
    result = GetRequest(url)
    Debug.Print result

    ' POST请求
    Dim postData As String
    postData = "{""key"":""value""}"
    result = PostRequest(url, postData)
    Debug.Print result
End Sub

六、注意事项

  1. 使用前需要创建XMLHTTP对象
  2. 确保URL地址正确
  3. 根据接口要求设置正确的请求头
  4. 处理响应数据时注意编码问题
  5. 对于HTTPS请求，可能需要额外的证书处理

七、参考链接


作者: CLP ; 日期: 2022-9-3 ; 地点: 武汉; 天气: 晴

QQ: 53258372; Mail : 53258372@QQ.com

微信: image-20220903114619121