如何将Google Sheets数据直接加载至数组而非Excel工作表？

完全可以跳过加载到工作表的步骤，直接把Google Sheets数据存入VBA数组，核心思路是请求Google Sheets的CSV导出接口获取纯文本数据，再解析CSV字符串为数组，具体实现如下：

实现步骤与代码 #

1. 准备可访问的导出链接：确保你的Google Sheets设置了「任何人可查看」权限，导出链接格式为：https://docs.google.com/spreadsheets/d/[表格ID]/export?format=csv&gid=[工作表GID]

   • 表格ID：Google Sheets地址栏中d/与/edit之间的字符串
   • 工作表GID：地址栏末尾gid=后面的数字，对应你要导出的目标工作表

2. 用VBA发送请求并解析数据到数组：

Sub GoogleSheetsToDirectArray()
    Dim http As Object
    Dim csvData As String
    Dim dataArray As Variant
    Dim rowData As Variant
    Dim i As Long, j As Long
    
    ' 创建HTTP请求对象
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    ' 替换为你的Google Sheets CSV导出链接
    Dim csvUrl As String
    csvUrl = "https://docs.google.com/spreadsheets/d/123abcXYZ/export?format=csv&gid=0"
    
    ' 发送GET请求获取CSV数据
    http.Open "GET", csvUrl, False
    http.Send
    
    ' 检查请求状态
    If http.Status <> 200 Then
        MsgBox "请求失败，状态码：" & http.Status
        Exit Sub
    End If
    
    ' 获取返回的CSV文本内容
    csvData = http.ResponseText
    
    ' 解析CSV文本为二维数组
    rowData = Split(csvData, vbCrLf)
    ' 初始化数组（按表头列数定义列维度）
    ReDim dataArray(1 To UBound(rowData), 1 To UBound(Split(rowData(0), ",")))
    
    ' 逐行拆分数据到数组
    For i = 0 To UBound(rowData)
        If Trim(rowData(i)) <> "" Then
            Dim colData As Variant
            colData = Split(rowData(i), ",")
            For j = 0 To UBound(colData)
                dataArray(i + 1, j + 1) = colData(j)
            Next j
        End If
    Next i
    
    ' 测试：输出数组第一行第一列数据
    MsgBox "数组第一行第一列：" & dataArray(1, 1)
    
    ' 后续可直接使用dataArray做数据处理
    ' ...
    
    Set http = Nothing
End Sub

注意事项 #

• 权限验证：必须确保Google Sheets开启了「任何人可查看」权限，否则请求会返回权限错误
• CSV解析优化：上述是基础版解析逻辑，如果数据包含逗号、换行符或引号，需要使用更严谨的CSV解析函数处理特殊字符
• 请求对象兼容：若MSXML2.XMLHTTP报错，可尝试替换为MSXML2.ServerXMLHTTP

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