使用VBA修复上传文件到Google Drive时出现未经授权的错误

通过VBA上传文件到Google Drive - 解决未经授权的错误

带有 Google Drive API 和基于令牌的身份验证的 VBA

Sub GoogleDriveAPI()
    Const reqURL As String = "https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart"
    Const Token As String = "api-token" ' Replace with your actual API token
    Dim content() As Byte, fPath As String, FileName As String
    Dim file_metadata As String
    fPath = "D:\" ' Path to the file to be uploaded
    FileName = "M.pdf" ' The file name
    file_metadata = "{'name':'" & FileName & "'}"
    ' Boundary for separating file parts
    Dim Boundary, part As String, ado As Object
    Boundary = CreateBoundary()
    part = BuildMultipartContent(Boundary, file_metadata, fPath, FileName)
    ' Create HTTP request for Google Drive API
    Dim req As New MSXML2.XMLHTTP60
    Set req = New MSXML2.ServerXMLHTTP60
    With req
        .Open "POST", reqURL, False
        .setRequestHeader "Authorization", "Bearer " & Token
        .setRequestHeader "Content-Type", "multipart/related; boundary=" & Boundary
        .send ado.Read
    End With
    If req.Status = 200 Then
        Debug.Print req.responseText ' Success
    Else
        MsgBox req.Status & ": " & req.statusText ' Error handling
    End If
End Sub

Function CreateBoundary() As String
    Dim s As String
    Dim n As Integer
    For n = 1 To 16
        s = s & Chr(65 + Int(Rnd * 25))
    Next
    CreateBoundary = s & CDbl(Now)
End Function

Function BuildMultipartContent(Boundary As String, metadata As String, fPath As String, FileName As String) As String
    Dim part As String
    part = "--" & Boundary & vbCrLf
    part = part & "Content-Type: application/json; charset=UTF-8" & vbCrLf & vbCrLf
    part = part & metadata & vbCrLf
    part = part & "--" & Boundary & vbCrLf
    part = part & "Content-Type: application/pdf" & vbCrLf
    part = part & "Content-Transfer-Encoding: binary" & vbCrLf & vbCrLf
    part = part & ReadBinaryFile(fPath, FileName) & vbCrLf
    part = part & "--" & Boundary & "--"
    BuildMultipartContent = part
End Function

Function ReadBinaryFile(fPath As String, FileName As String) As String
    Dim ado As Object
    Set ado = CreateObject("ADODB.Stream")
    ado.Type = 1 ' Binary mode
    ado.Open
    ado.LoadFromFile fPath & FileName
    ReadBinaryFile = ado.Read
    ado.Close
End Function

替代方法：使用 Python 通过 Google Drive API 上传文件

Python 与 Google Drive API 和 OAuth 2.0 进行身份验证

from google.oauth2 import service_account
from googleapiclient.discovery import build
from googleapiclient.http import MediaFileUpload

def upload_to_drive():
    credentials = service_account.Credentials.from_service_account_file('path-to-credentials.json')
    service = build('drive', 'v3', credentials=credentials)

    file_metadata = {'name': 'M.pdf'}
    media = MediaFileUpload('D:/M.pdf', mimetype='application/pdf')

    file = service.files().create(body=file_metadata, media_body=media, fields='id').execute()
    print('File ID: %s' % file.get('id'))

if __name__ == '__main__':
    upload_to_drive()

有关使用 VBA 将文件上传到 Google 云端硬盘的常见问题

1. 如何解决 VBA 脚本中的“未经授权”错误？
2. 确保您使用的 API 令牌具有正确的权限并且未过期。您可以刷新令牌或使用 OAuth 2.0 流生成一个新的。
3. 多部分请求中边界的目的是什么？
4. 边界是一个唯一的字符串，用于分隔多部分数据的不同部分。它帮助 API 在使用时区分文件元数据和文件内容 multipart/related 请求。
5. 为什么我的文件无法正确上传？
6. 这可能是由于多部分数据的格式不正确或文件路径无效造成的。使用 ADODB.Stream 以二进制格式读取文件并确保路径正确。
7. 如何检查 Google Drive API 的响应？
8. 您可以使用 Debug.Print 在 VBA 编辑器的即时窗口中显示服务器的响应。这有助于了解请求是否成功或是否存在错误。
9. 使用 VBA 将文件上传到 Google Drive 时有哪些常见错误？
10. 一些常见的错误包括使用过期的 API 令牌、HTTP 请求的格式不正确或未包含必要的内容 Authorization 标头。

总结 VBA Google Drive 上传指南

总之，通过 VBA 成功将文件从 Excel 上传到 Google Drive 需要仔细注意身份验证和格式设置。使用准确的令牌和正确的 API 设置对于避免“未经授权”等常见错误至关重要。

此外，确保正确构造多部分请求并有效处理二进制文件数据将使过程顺利且无错误。通过正确的方法和错误处理技术，这些任务可以在 Excel 中无缝自动化。