How to Automatically Link Future SharePoint Excel Files Using Power Query
Question details
The user needs to dynamically combine future monthly Excel workbooks stored on SharePoint into a master workbook, bypassing the limitation where standard external formulas cannot link to files that have not yet been created.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Consolidating recurring monthly reports from a SharePoint folder into a master file without manually updating links or opening the source workbooks every month.
- Observed behavior
- Using standard formula links (like ='[West-February.26.xlsb]MonthlyBudget'!A1) results in a #REF! error because the source workbook does not exist yet.
Ensure you have the exact root URL of your SharePoint site and the necessary account permissions to access the target folder. Confirm that all future workbooks will follow a consistent naming convention and internal table structure.
Use Power Query to Connect to the SharePoint Folder
By connecting Power Query directly to the SharePoint folder, Excel will automatically scan for new files, filter out the ones you need, and combine their data upon a simple refresh.
Power Query evaluates the contents of a folder rather than a single static file path. When a new file drops into the SharePoint folder, Power Query simply appends it during the next data refresh cycle.
For this method to work seamlessly, every monthly file uploaded must contain identical column headers and sheet structures.
Open your master Excel workbook. Navigate to the Data tab on the ribbon, click 'Get Data', choose 'From File', and select 'From SharePoint Folder'.
In the prompt, enter the root URL of your SharePoint site (e.g., https://yourdomain.sharepoint.com/sites/YourSite) without the specific document library path, and click OK.
When the file preview appears, click 'Transform Data'. In the Power Query Editor, use the 'Folder Path' and 'Name' columns to filter the list down so it only displays the monthly report files you want to merge.
Click the 'Combine Files' icon (two downward arrows) located on the header of the 'Content' column. Select the specific sheet or table structure that applies to all files.
Click 'Close & Load' in the top-left corner. Whenever a new file is uploaded to SharePoint in the future, simply go to the Data tab and click 'Refresh All' to update the master file.
Use VBA Macros to Dynamically Generate Links
If you cannot use Power Query, a VBA script can be programmed to check the SharePoint directory and construct formula links only if the target file has been created.
Try WPS Office for Seamless Data Management
While advanced SharePoint folder integrations heavily rely on Microsoft's ecosystem, WPS Office provides a free, lightweight, and highly compatible alternative for handling complex spreadsheets, consolidating data locally, and managing everyday office tasks without an expensive subscription.
- 1. Download and Install: Visit the official WPS website to download the free WPS Office suite for your operating system.
- 2. Open Your Workbooks: Launch WPS Spreadsheet and open your existing Excel workbooks. The formatting and standard formulas remain completely intact.
- 3. Consolidate Data: Navigate to the Data tab and utilize the 'Consolidate' tool to effortlessly summarize and combine data ranges from multiple local workbooks.
Frequently Asked Questions
Why do I get a #REF! error when linking to a future Excel file?
Standard Excel formulas evaluate immediately upon creation. If the destination file path does not exist at the exact moment you write the formula, Excel cannot resolve the reference and returns a #REF! error.
Do all future SharePoint files need to have the exact same format for Power Query to work?
Yes. When Power Query combines files from a folder, it relies on a consistent column layout and sheet/table structure across all workbooks to append the data accurately.
How do I update my master file when a new month's report is added to SharePoint?
Once your Power Query connection is set up, you only need to open the master workbook, navigate to the Data tab on the ribbon, and click 'Refresh All'. Power Query will automatically detect the new file, extract its data, and update your master table.
Can I filter out certain files in the SharePoint folder from being combined?
Yes. Before clicking 'Combine Files' in the Power Query Editor, you can apply text or date filters to columns like 'Name' or 'Folder Path'. This ensures only the relevant monthly reports are processed, ignoring unrelated documents in the same directory.
