Fix Power Query Cannot Connect to Excel File in OneDrive
Question details
The user is unable to connect to an Excel file hosted on a school OneDrive using Power Query due to web authentication failures.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to retrieve data from an online OneDrive Excel file via Power Query or VBA.
- Observed behavior
- The data connection works locally but fails during the web authentication step when connecting to the school OneDrive data source.
Before troubleshooting, ensure you are signed into Excel with your school or organizational account and have the exact URL of your OneDrive file.
Configure Organizational Credentials in Data Source Settings
Clear any incorrect cached credentials and explicitly sign in using your organizational account to authenticate with the school OneDrive.
Power Query often caches login attempts. If you previously tried connecting using 'Anonymous' or 'Windows' credentials, the connection will fail for a school OneDrive account.
Open Excel, navigate to the 'Data' tab on the ribbon, click on 'Get Data', and select 'Data Source Settings'.
Select 'Global permissions' at the top, locate the URL corresponding to your school OneDrive or SharePoint, and click 'Clear Permissions'.
Attempt to connect to your web data source again. When the credentials prompt appears, select 'Organizational account' from the left sidebar and click 'Sign In'.
Format the OneDrive Web File Path Correctly
Ensure the copied URL points directly to the file data without web preview parameters, which can block Power Query from authenticating.
Update VBA Connection Strings for Cloud Files
If connecting via VBA instead of the manual interface, ensure the script references the SharePoint/OneDrive HTTPS URL rather than a local sync path.
Experience Hassle-Free Data Management with WPS Office
If complex Power Query connections and Microsoft Excel authentication errors are slowing you down, consider switching to WPS Office. It provides a lightweight, highly compatible alternative for handling spreadsheets and data analysis without the steep learning curve or complicated credential setups.
- 1. Download WPS Office: Visit the official WPS website and download the free office suite.
- 2. Install and Launch: Follow the straightforward installation wizard and open WPS Spreadsheets.
- 3. Open Your Data Files: Easily import your existing Excel files and continue your data tasks without complex configurations.
Frequently Asked Questions
Why does Power Query show an authentication error when connecting to my school OneDrive?
This typically occurs if Power Query is attempting to use 'Anonymous' or 'Windows' credentials by default instead of an 'Organizational account'. You must clear your data source permissions and re-authenticate using your designated school email.
How do I clear my cached credentials in Power Query?
Go to the 'Data' tab on the Excel ribbon, select 'Get Data', and click 'Data Source Settings'. Choose 'Global permissions', select the OneDrive URL causing the issue, and click 'Clear Permissions'.
Can I connect Power Query to a personal OneDrive account?
Yes, but the process differs from school or enterprise accounts. You typically need to generate an embed link from the personal OneDrive file and use the 'Web' connection option in Power Query, ensuring you remove the '?web=1' tag from the URL.
Why does my VBA macro work for local files but fail for OneDrive web files?
VBA macros using standard local file paths (like C:\Users\...) only work for locally synced files. To connect to a file hosted on OneDrive via the web, the VBA code must be explicitly updated to use the HTTPS SharePoint or OneDrive URL.
