通过Google Drive API从MS Access上传图片至谷歌云端硬盘格式异常求助

问题说明 #

• 已通过VBA调用Google Drive API完成图片上传，但上传的文件实际是Base64格式的文本，而非原始二进制图片
• 上传后无法在Google Drive直接预览或打开，下载后需将扩展名从.jpg改为.txt才能看到Base64字符串内容
• 该Base64字符串可通过HTML的img标签正常显示（使用data:image/jpg;base64,前缀）

错误原因 #

当前代码的核心问题是：将图片转为Base64字符串后，直接作为multipart请求的二进制部分内容发送。Google Drive接收到的是文本格式的Base64数据，而非原始的图片二进制流，因此无法识别为图片文件。

修正后的代码 #

解决思路是直接发送图片的原始字节数组，跳过Base64编码步骤，同时正确构建multipart/form-data请求的二进制内容：

Option Compare Database

Sub UploadFileToGoogleDrive_Fixed()
    Dim imageFile As String
    Dim imageBytes() As Byte
    Dim boundary As String
    Dim request As Object
    Dim accessToken As String
    Dim folderId As String
    Dim requestHeader() As Byte
    Dim requestFooter() As Byte
    Dim fullRequest() As Byte
    
    ' 配置参数
    accessToken = "ya29.a0Ad52N3_EtFDYr_3lTO-i1P0sNbqgUXzvp..........."
    imageFile = Forms!PatientFormF2!PatientPhotoPath.Value
    folderId = "1EptP5DEg_m2DE1N67sQ........"
    
    ' 读取图片原始字节数组
    Open imageFile For Binary As #1
    ReDim imageBytes(LOF(1) - 1)
    Get #1, , imageBytes
    Close #1
    
    ' 构建multipart边界
    boundary = "---------------------------" & Format(Now, "hhmmss") & "abcd"
    
    ' 构建请求头部分（转为字节数组）
    Dim headerText As String
    headerText = "--" & boundary & vbCrLf & _
                 "Content-Type: application/json; charset=UTF-8" & vbCrLf & vbCrLf & _
                 "{""name"": ""uploaded_image.jpg"", ""parents"": [""" & folderId & """]}" & vbCrLf & vbCrLf & _
                 "--" & boundary & vbCrLf & _
                 "Content-Type: image/jpeg" & vbCrLf & vbCrLf
    requestHeader = StrConv(headerText, vbFromUnicode)
    
    ' 构建请求尾部分（转为字节数组）
    Dim footerText As String
    footerText = vbCrLf & "--" & boundary & "--"
    requestFooter = StrConv(footerText, vbFromUnicode)
    
    ' 合并所有部分为完整的请求字节数组
    ReDim fullRequest(UBound(requestHeader) + UBound(imageBytes) + UBound(requestFooter) + 2)
    Call CopyMemory(fullRequest(0), requestHeader(0), UBound(requestHeader) + 1)
    Call CopyMemory(fullRequest(UBound(requestHeader) + 1), imageBytes(0), UBound(imageBytes) + 1)
    Call CopyMemory(fullRequest(UBound(requestHeader) + UBound(imageBytes) + 2), requestFooter(0), UBound(requestFooter) + 1)
    
    ' 创建并配置HTTP请求
    Set request = CreateObject("MSXML2.XMLHTTP")
    request.Open "POST", "https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart", False
    request.setRequestHeader "Authorization", "Bearer " & accessToken
    request.setRequestHeader "Content-Type", "multipart/form-data; boundary=" & boundary
    
    ' 发送二进制请求数据
    request.Send fullRequest
    
    ' 处理响应
    If request.status = 200 Then
        MsgBox "文件上传成功！"
    Else
        MsgBox "上传失败：" & request.StatusText & " (" & request.status & ")"
    End If
    
    Set request = Nothing
End Sub

' 需要添加API声明用于字节数组复制
Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
    ByRef Destination As Any, _
    ByRef Source As Any, _
    ByVal Length As LongPtr _
)

关键修改点 #

1. 移除Base64编码：直接使用图片的原始字节数组，避免将二进制转为文本格式
2. 二进制请求拼接：通过CopyMemory合并multipart头部、图片字节、尾部，确保二进制数据不被篡改
3. 修正Content-Length：XMLHTTP会自动计算并设置Content-Length，无需手动指定（原代码中postData未定义，属于错误）
4. 正确的请求发送：调用request.Send时传入字节数组，而非字符串，确保Google Drive接收原始二进制流

内容的提问来源于stack exchange，提问作者Fakir Rifat Hasan