在MS Access中用VBA-WEB结合cURL发送GET JSON请求遇错求助

Hey @TimHall, hoping you can share some practical insights here to help out! Let’s walk through how to translate your cURL GET request logic into VBA-WEB code for MS Access, plus save the JSON response to a text file ready for JsonConverter.ParseJson.

Step 1: 确认VBA-WEB工具已正确配置 #

First, double-check that you’ve got all core VBA-WEB modules imported into your Access project (since you mentioned following the setup guide, this is just a quick sanity check). Make sure modules like WebClient, WebRequest, and WebResponse are present and free of compile errors.

Step 2: 把cURL请求转化为VBA-WEB代码 #

Let’s use a typical cURL example to map directly to VBA-WEB syntax. Suppose your cURL command looks like this:

curl -X GET "https://your-api-endpoint.com/data?status=active&count=20" \
  -H "Authorization: Bearer your-auth-token" \
  -H "Accept: application/json"

Here’s how to replicate this logic in VBA with VBA-WEB:

Sub SendGetRequestWithVBAWEB()
    Dim client As New WebClient
    Dim request As New WebRequest
    Dim response As WebResponse
    Dim jsonResponse As String
    Dim savePath As String
    
    ' --- 1. Configure client and request details ---
    ' Set base API URL (you can also use the full URL in request.Resource if preferred)
    client.BaseUrl = "https://your-api-endpoint.com"
    
    ' Define request method and target resource + query params
    request.Resource = "data"
    request.Method = WebMethod.HttpGet
    
    ' Add query parameters (matches the ?status=active&count=20 in your cURL)
    request.AddQueryParam "status", "active"
    request.AddQueryParam "count", 20
    
    ' Add required headers (matches the -H flags from cURL)
    request.AddHeader "Authorization", "Bearer your-auth-token"
    request.AddHeader "Accept", "application/json"
    
    ' --- 2. Execute the request ---
    On Error Resume Next ' Basic error handling; expand as needed for your use case
    Set response = client.Execute(request)
    On Error GoTo 0
    
    ' Check if the request succeeded
    If response.StatusCode = WebStatusCode.Ok Then
        jsonResponse = response.Content
        
        ' --- 3. Save JSON response to local text file ---
        savePath = "C:\Your\Local\Folder\api-response.json.txt" ' Update to your desired path
        
        ' Option 1: Use FileSystemObject for UTF-8 encoding
        Dim fso As Object
        Set fso = CreateObject("Scripting.FileSystemObject")
        Dim outputFile As Object
        Set outputFile = fso.CreateTextFile(savePath, True, True) ' True = overwrite, True = UTF-8
        outputFile.Write jsonResponse
        outputFile.Close
        
        ' Option 2: Native VBA Open statement (simpler, defaults to system encoding)
        ' Open savePath For Output As #1
        ' Print #1, jsonResponse
        ' Close #1
        
        Debug.Print "JSON saved successfully to: " & savePath
    Else
        Debug.Print "Request failed. Status Code: " & response.StatusCode & ", Message: " & response.StatusDescription
    End If
End Sub

Step 3: 用JsonConverter.ParseJson读取保存的文件 #

Once the JSON is saved to your text file, you can load and parse it like this:

Sub ParseSavedJsonData()
    Dim savePath As String
    Dim jsonText As String
    Dim jsonObj As Object
    
    savePath = "C:\Your\Local\Folder\api-response.json.txt" ' Match the path from Step 2
    
    ' Read the text file content
    Open savePath For Input As #1
    jsonText = Input$(LOF(1), 1)
    Close #1
    
    ' Parse the JSON with JsonConverter
    Set jsonObj = JsonConverter.ParseJson(jsonText)
    
    ' Example: Access parsed data (adjust based on your JSON structure)
    Debug.Print "First record ID: " & jsonObj("records")(1)("id")
    Debug.Print "Total records returned: " & jsonObj("metadata")("totalCount")
End Sub

关键注意事项 #

• Query Parameters: For GET requests, always pass parameters via AddQueryParam—POST requests would use AddBodyParam instead.
• Headers: Don’t skip required headers (like authorization tokens) from your cURL command—VBA-WEB won’t add these automatically unless you set global defaults in the WebClient.
• Encoding: Saving the file as UTF-8 (using the FSO method’s third True parameter) prevents garbled text if your JSON includes special characters.
• Error Handling: Expand the basic error handling to catch network timeouts, invalid credentials, or malformed responses—this will make your code more reliable for production use.

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