如何通过VBA从SharePoint 2016的Excel文件提取数据并汇总？

Hey there! Let's work through this SharePoint 2016 Excel aggregation challenge you're dealing with. It makes total sense that the regular path-based file access is throwing errors—SharePoint's security model doesn't play nice with that approach most of the time. Here are a few reliable methods to pull data from multiple Excel files into your summary sheet:

This is the simplest approach for most users, and it plays nicely with SharePoint's permissions:

• Open your summary Excel sheet on the SharePoint site.
• Go to the Data tab > Get Data > From File > From SharePoint Folder.
• Enter your SharePoint site's full URL (e.g., https://your-company.sharepoint.com/sites/your-team-site) and follow the prompts to select the folder containing your source Excel files.
• Power Query will load a list of all files in the folder. Click Combine & Load > Combine & Load To to merge all the data from matching sheets into your summary workbook.
• Pro tip: This method uses your current SharePoint permissions directly, so you won't hit security blocks as long as you can view all the source files. It also lets you refresh the data with one click later on.

If you need more control over how data is extracted/transformed, use the SharePoint Client Object Model (CSOM) in VBA—this avoids the path-based security issues:
First, enable the CSOM reference in your VBA editor:

• Open the VBA editor (Alt + F11) > Tools > References > Check Microsoft SharePoint Client and Microsoft SharePoint Client Runtime.

Then use this sample code as a starting point:

Sub AggregateSharePointExcelData()
    Dim siteUrl As String
    Dim sourceFolderRelativePath As String
    ' Replace these with your actual site and folder paths
    siteUrl = "https://your-company.sharepoint.com/sites/your-team-site"
    sourceFolderRelativePath = "/sites/your-team-site/Shared Documents/Source-Excel-Files"

    ' Initialize SharePoint client context
    Dim ctx As New ClientContext
    ctx.Url = siteUrl
    ' Uses your current Windows/SharePoint credentials automatically
    ctx.AuthenticationMode = ClientAuthenticationMode.Default

    ' Get the target folder and its files
    Dim targetFolder As Folder
    Set targetFolder = ctx.Web.GetFolderByServerRelativeUrl(sourceFolderRelativePath)
    ctx.Load(targetFolder.Files)
    ctx.ExecuteQuery ' Fetch data from SharePoint

    Dim spFile As File
    Dim srcWorkbook As Workbook
    Dim summarySheet As Worksheet
    Set summarySheet = ThisWorkbook.Sheets("Summary") ' Your summary sheet name

    ' Loop through each Excel file in the folder
    For Each spFile In targetFolder.Files
        Dim fileExt As String
        fileExt = LCase(Right(spFile.Name, 5))
        If fileExt = ".xlsx" Or fileExt = ".xlsm" Then
            ' Use the full SharePoint URL to open the file
            Dim fileFullUrl As String
            fileFullUrl = siteUrl & spFile.ServerRelativeUrl

            ' Open the source workbook (read-only to avoid locks)
            Set srcWorkbook = Workbooks.Open(fileFullUrl, ReadOnly:=True)
            
            ' --- Add your custom data extraction logic here ---
            ' Example: Copy data from Sheet1 to the next empty row in summary
            srcWorkbook.Sheets("Sheet1").UsedRange.Copy _
                Destination:=summarySheet.Cells(summarySheet.Rows.Count, 1).End(xlUp).Offset(1, 0)
            
            ' Close the source workbook without saving changes
            srcWorkbook.Close SaveChanges:=False
        End If
    Next spFile

    Set ctx = Nothing
    MsgBox "Data aggregation complete!", vbInformation
End Sub

• Security note: This method uses official SharePoint APIs, so it respects site permissions and avoids the "untrusted path" errors you were seeing. Just make sure your VBA macro settings allow signed/trustworthy macros to run.

If you need to schedule this task or handle a huge number of files, PowerShell with PnP modules is a great option:

1. Install the required modules first:

   Install-Module -Name PnP.PowerShell -Force
   Install-Module -Name ImportExcel -Force
   

2. Use this script as a template:

   # Configuration - Update these values
   $siteUrl = "https://your-company.sharepoint.com/sites/your-team-site"
   $sourceFolderRelativePath = "/sites/your-team-site/Shared Documents/Source-Excel-Files"
   $summaryFileRelativePath = "/sites/your-team-site/Shared Documents/Summary.xlsx"
   $tempLocalDir = "C:\Temp\SPExcelTemp"
   New-Item -Path $tempLocalDir -ItemType Directory -Force | Out-Null
   
   # Connect to SharePoint using your current credentials
   Connect-PnPOnline -Url $siteUrl -UseWebLogin
   
   # Get all Excel files from the source folder
   $excelFiles = Get-PnPFile -Folder $sourceFolderRelativePath -Recurse | 
       Where-Object { $_.Name -match "\.xlsx$|\.xlsm$" }
   
   # Initialize a temporary summary file
   $tempSummaryPath = Join-Path $tempLocalDir "Temp-Summary.xlsx"
   if (Test-Path $tempSummaryPath) { Remove-Item $tempSummaryPath -Force }
   
   # Process each file
   foreach ($file in $excelFiles) {
       $localFilePath = Join-Path $tempLocalDir $file.Name
       # Download the file to temp directory
       Get-PnPFile -Url $file.ServerRelativeUrl -Path $tempLocalDir -FileName $file.Name -AsFile
       
       # Import data from the source sheet (adjust sheet name as needed)
       $fileData = Import-Excel -Path $localFilePath -WorksheetName "Sheet1"
       # Append data to the temporary summary
       Export-Excel -Path $tempSummaryPath -InputObject $fileData -Append -WorksheetName "AggregatedData"
       
       # Clean up local temp file
       Remove-Item $localFilePath -Force
   }
   
   # Upload the final summary back to SharePoint (overwrites existing file)
   Set-PnPFile -Path $tempSummaryPath -Url $summaryFileRelativePath -Overwrite
   
   # Clean up temp files
   Remove-Item $tempSummaryPath -Force
   Write-Host "Aggregation completed successfully!" -ForegroundColor Green
   

• Stop using network share paths like \\sharepoint-server\sites\...—these often trigger security restrictions. Always use the HTTPS site URL instead.
• Double-check your permissions: Ensure you have Read access to all source Excel files and Edit access to the summary sheet.
• If source files are password-protected, you'll need to add code to handle that (Power Query has options for this, and VBA/PowerShell can include password parameters).

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