如何用VBA从多个SharePoint Excel文件抓取数据补全表格？

核心思路 #

• 遍历所有目标SharePoint文件链接
• 按「Mon/Tue」等星期工作表名称匹配，确保抓取同一天的数据
• 通过D列的唯一车辆编码关联数据，自动补全L列的集装箱数量

代码示例（直接可用，按需修改） #

Sub FetchSPData()
    Dim spFileLinks As Variant
    Dim targetWB As Workbook, currentWB As Workbook
    Dim targetWS As Worksheet, currentWS As Worksheet
    Dim lastRow As Long, i As Long
    Dim carID As String
    Dim matchRow As Variant
    
    ' 绑定当前你的场站记录文件
    Set currentWB = ThisWorkbook
    
    ' 替换成实际的所有场站SharePoint文件链接
    spFileLinks = Array( _
        "https://your-sharepoint-site/sites/xxx/场站A.xlsx", _
        "https://your-sharepoint-site/sites/xxx/场站B.xlsx" _
    )
    
    ' 关闭屏幕刷新，提升运行速度
    Application.ScreenUpdating = False
    
    ' 遍历每个外部场站文件
    For Each link In spFileLinks
        ' 只读打开，避免锁定文件影响其他场站编辑
        Set targetWB = Workbooks.Open(link, ReadOnly:=True)
        
        ' 遍历当前文件的所有星期工作表
        For Each currentWS In currentWB.Worksheets
            ' 匹配外部文件的同名称工作表
            On Error Resume Next
            Set targetWS = targetWB.Worksheets(currentWS.Name)
            On Error GoTo 0
            
            If Not targetWS Is Nothing Then
                lastRow = currentWS.Cells(currentWS.Rows.Count, "D").End(xlUp).Row
                
                ' 从第2行开始遍历（假设第1行是表头）
                For i = 2 To lastRow
                    carID = currentWS.Cells(i, "D").Value
                    If carID <> "" Then
                        ' 快速查找对应车辆编码的行
                        matchRow = Application.Match(carID, targetWS.Columns("D"), 0)
                        
                        ' 找到匹配则补全L列数据
                        If Not IsError(matchRow) Then
                            currentWS.Cells(i, "L").Value = targetWS.Cells(matchRow, "L").Value
                        End If
                    End If
                Next i
            End If
            Set targetWS = Nothing
        Next currentWS
        
        ' 关闭外部文件，不保存（只读打开无需保存）
        targetWB.Close SaveChanges:=False
    Next link
    
    Application.ScreenUpdating = True
    MsgBox "数据补全完成！", vbInformation
End Sub

关键细节解释 #

• SharePoint文件访问：直接用Workbooks.Open传入SharePoint链接即可，必须设置ReadOnly:=True，防止和其他场站的编辑操作冲突。
• 工作表匹配：通过工作表名称（Mon/Tue）精准对应，保证抓取的是同一天的到发数据。
• 高效匹配：用Application.Match替代循环遍历查找，数据量大时效率提升明显；如果找不到匹配项，IsError会跳过，避免报错。
• 错误处理：用On Error Resume Next处理找不到对应工作表的情况，比如某场站当天没数据、工作表名称不一致，程序会自动跳过继续执行。

入门级优化建议 #

• 链接管理：别硬编码链接，把所有场站的SharePoint链接放在当前文件的一个单独工作表（比如叫「配置表」）里，用代码读取这个表的链接，后续新增/修改场站直接改表格就行。
• 增量更新：给你的表格加个「更新状态」列，标记已完成匹配的行，下次运行只处理未标记的行，减少遍历量。
• 一键执行：在Excel界面加个按钮，把这个宏绑定上去，不用每次打开VBA编辑器运行。
• 日志记录：新增一个「日志表」，记录哪些车辆匹配成功、哪些没找到对应数据，方便后续核对。

注意事项 #

• 确保你对所有目标SharePoint文件有读取权限，否则会提示无法打开。
• 运行前关闭所有目标文件，避免打开冲突。
• 如果数据量特别大，可以在代码开头加Application.Calculation = xlCalculationManual，结尾改回xlCalculationAutomatic，进一步提升速度。

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