使用VBA向ASP.NET Core Web API上传文件遇到问题

核心结论：必须将文件内容加入payload，仅传递本地文件名无效——后端Web API无法访问你的本地文件系统，必须通过请求体传输实际的文件数据。

原VBA代码的问题点 #

• Content-Disposition格式错误：字段名应为name="file"（对应后端IFormFile file参数），且需添加filename属性传递文件名
• Multipart分隔符格式错误：正确的分隔符应为--{boundary}，结尾需用--{boundary}--闭合
• 缺少文件类型头：需添加Content-Type: text/csv告知后端文件类型
• 变量声明不规范：Dim filename, resp As String中filename会被识别为Variant类型，需明确指定类型
• 文件读取方式可能存在编码问题：文本模式读取可能丢失二进制信息，建议用二进制模式读取

修正后的VBA代码 #

Sub Upload()
    Dim objHTTP As New MSXML2.ServerXMLHTTP60
    Dim url As String
    Dim filename As String, resp As String, contents As String
    Dim boundary As String, payload As String

    ' 本地CSV文件路径
    filename = "C:\temp\File2.csv"
    ' 生成唯一分隔符（避免冲突）
    boundary = "---------------------------" & Format(Now, "YYYYMMDDHHMMSS")
    ' API地址（bkm_volume对应后端的path参数）
    url = "https://localhost:7102/api/v1/SThree/upload/bkm_volume"

    ' 读取文件内容（二进制模式确保数据完整）
    Open filename For Binary As #1
    contents = Space$(LOF(1))
    Get #1, , contents
    Close #1

    ' 构建multipart/form-data请求体
    payload = "--" & boundary & vbCrLf & _
              "Content-Disposition: form-data; name=""file""; filename=""" & Dir(filename) & """" & vbCrLf & _
              "Content-Type: text/csv" & vbCrLf & vbCrLf & _
              contents & vbCrLf & _
              "--" & boundary & "--"

    ' 发送请求
    objHTTP.Open "POST", url, False
    objHTTP.setRequestHeader "accept", "*/*"
    objHTTP.setRequestHeader "Content-Type", "multipart/form-data; boundary=" & boundary
    objHTTP.setRequestHeader "Content-Length", LenB(payload)
    objHTTP.send payload

    ' 处理响应
    If objHTTP.Status = 200 Then
        MsgBox "文件上传成功"
    Else
        resp = objHTTP.responseText
        MsgBox "上传失败：" & resp
    End If
End Sub

后端接口注意事项 #

1. 确保路由配置正确：如果path是URL路由参数，控制器需添加对应路由特性，示例：

   [ApiController]
   [Route("api/v1/SThree/upload/{path}")]
   public class SThreeController : ControllerBase
   {
       [HttpPost]
       public async Task<IActionResult> UploadFile(IFormFile? file, string path)
       {
           if (file == null || file.Length == 0)
           {
               return BadRequest("No file provided.");
           }
           
           if (file.Length > Definitions.MaxFileSize)
           {
               return BadRequest("Invalid path. The specified file was too large.");
           }
   
           var outputDirectory = Path.Combine(Path.GetTempPath(), "UploadFiles");
           var tempPath = Path.Combine(outputDirectory, file.FileName);
   
           if (!Directory.Exists(outputDirectory))
           {
               Directory.CreateDirectory(outputDirectory);
           }
   
           await using (var stream = new FileStream(tempPath, FileMode.Create))
           {
               await file.CopyToAsync(stream);
           }
   
           return Ok("File uploaded successfully"); // 补充返回值
       }
   }
   

2. 原后端接口缺少返回语句，需补充return Ok()或其他响应，否则会触发服务器错误

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