How to Connect Excel VBA ADODB to an Excel File on SharePoint
Question details
The user needs a working method to establish an ADODB connection via Excel VBA to query an Excel workbook that is stored on a SharePoint site.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A developer is writing a VBA macro to retrieve data from a centralized Excel file hosted on SharePoint using an ADODB connection.
- Observed behavior
- The ADODB connection fails and throws an error because the ACE OLE DB provider does not recognize a SharePoint HTTP/HTTPS web URL or a converted network path as a valid data source.
Ensure you have administrator access to Microsoft Entra ID (formerly Azure AD) to register an application and obtain the required Microsoft Graph API credentials.
Authenticate via Microsoft Graph and Download to a Temporary Path
Since ACE OLE DB cannot directly query a SharePoint URL, the most reliable approach is to use Microsoft Graph API to download a temporary local copy of the file for ADODB to query.
This solution relies on registering an Azure application to securely acquire an access token. Using this token, your VBA script downloads the SharePoint workbook locally so the standard ACE OLE DB driver can process the query.
Log into the Azure Portal, open Microsoft Entra ID, and register a new application. Note the generated Client ID and create a new Client Secret.
Navigate to API permissions in your registered app. Add Microsoft Graph permissions such as 'Files.Read.All' or 'Sites.Read.All', and ensure you grant admin consent.
In your Excel VBA editor, use the MSXML2.XMLHTTP object to send a POST request to the Microsoft identity platform using your Client ID and Secret to retrieve a Bearer access token.
Use the Bearer token to make an API call to the specific SharePoint site and drive endpoints. Save the binary response body to a temporary local path, such as your Windows Temp folder.
Set up your ADODB connection string using the 'Microsoft.ACE.OLEDB.12.0' provider, pointing the 'Data Source' argument to the temporary local file path you just created.

Try WPS Office for Seamless Spreadsheet Management
While integrating Microsoft Excel VBA with SharePoint requires complex Graph API authentications, managing your everyday spreadsheets shouldn't be a struggle. WPS Office provides a free, highly compatible alternative for viewing, editing, and managing your data with built-in cloud integrations.
- 1. Download and Install WPS Office: Visit the official WPS website, download the free installer, and complete the rapid setup process.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheets and open any existing Excel workbook. Your formatting, data, and standard functions will remain perfectly intact.
- 3. Save to Cloud or Local: Edit your documents smoothly and save them locally, or take advantage of WPS Cloud for instant backup and access anywhere.

Frequently Asked Questions
Why does my ADODB connection string fail with a SharePoint URL?
The Microsoft ACE OLE DB provider is designed to read local file paths or valid UNC network paths. It cannot natively resolve or authenticate against HTTP/HTTPS web URLs used by SharePoint, returning an invalid data source error.
Can I map a SharePoint document library to a network drive for ADODB?
While you can map a SharePoint library via WebDAV to act like a local network drive, this method is highly unstable, prone to timeout issues, and requires an active authenticated user session in Internet Explorer or Edge, making it unreliable for automated VBA macros.
Is it possible to query SharePoint Excel data without downloading the whole file?
Not natively using ADODB. However, you can achieve this by using Power Query within Excel to connect directly to the SharePoint folder, or by using the Microsoft Graph API's specific Excel endpoints to read worksheet ranges directly via HTTP requests.
What happens if I don't grant Admin Consent to the Entra ID application?
If Admin Consent is missing for application-level permissions like Files.Read.All, the Graph API will reject your VBA authentication request with an 'Unauthorized' or 'Access Denied' error, preventing the file download.




