'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Transfer Data Between Excel Files on Different SharePoint Drives
Question details
The user wants to automate the transfer of specific data between two Excel files stored on separate SharePoint Online drives that belong to different users.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Automating data extraction and integration across remote SharePoint environments without relying on manual copy-pasting.
- Observed behavior
- The user needs to establish a live connection to retrieve data from a source file on a different SharePoint drive and keep the destination workbook updated.
Verify that you have been granted at least Read access to the specific Excel files by the owners of the respective SharePoint drives, and have the exact file URLs ready.
Use Power Query to Connect to the SharePoint Excel File
Using Power Query's 'Get Data from Web' feature allows you to establish a direct, refreshable link to an Excel file hosted on any accessible SharePoint drive.
Power Query provides a robust way to link workbooks across cloud storage environments. By treating the SharePoint file path as a web data source, you can bypass local syncing requirements and authenticate securely using your organizational credentials.
Open the source Excel file located on the external SharePoint drive using the Excel desktop app. Go to 'File' > 'Info' and click 'Copy Path'. Ensure you delete the '?web=1' parameter at the end of the URL before using it.
Open your destination Excel workbook. Navigate to the 'Data' tab on the ribbon, click on 'Get Data', select 'From Other Sources', and then choose 'From Web'.
Paste the copied file path into the URL box and click 'OK'. When prompted for credentials, select 'Organizational account' from the left sidebar, click 'Sign in', and authenticate with your Microsoft account that has permission to access the file.
In the Navigator window, browse the components of the source workbook and select the specific table or sheet you want to transfer. Click 'Load' to import it directly into your current workbook, or 'Transform Data' to filter it first.
Need a Powerful, Free Alternative to Manage Your Spreadsheets?
While advanced SharePoint integrations require expensive Microsoft 365 subscriptions, WPS Office offers a free, lightweight, and incredibly powerful productivity suite. WPS Spreadsheets is perfect for managing complex data sets, offering high compatibility with Excel formats and a familiar interface.
- 1. Download and Install: Visit the official WPS website, download the free installation package for your OS, and follow the on-screen instructions.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheets and easily open your existing .xlsx files with full formatting retention.
- 3. Manage Data Efficiently: Utilize built-in data tools, formulas, and charts to process your spreadsheet data flawlessly.
Frequently Asked Questions
Do I need special permissions to pull data from another user's SharePoint drive?
Yes, you cannot bypass SharePoint security. The owner of the source SharePoint drive must explicitly grant your Microsoft account at least 'Read' access to the file or the folder containing it.
Why does my Power Query SharePoint link end with ?web=1 and fail to connect?
The '?web=1' suffix is appended to force the file to open in Excel Online through a browser. You must delete this query parameter from the URL before pasting it into Power Query to ensure it connects directly to the raw file.
Can I set the transferred data to update automatically on a schedule?
Yes. After setting up the connection, go to the 'Data' tab, click 'Queries & Connections', right-click your query, and select 'Properties'. Here you can enable options like 'Refresh data when opening the file' or set it to refresh automatically every specific number of minutes.
