VBA & Macro Problems

How to Connect Excel VBA ADODB to an Excel File on SharePoint

Adam DavisAdam Davis Oct 14, 2026 870 views

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.

How to Connect Excel VBA ADODB to a SharePoint Excel File
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.
Before you start

Ensure you have administrator access to Microsoft Entra ID (formerly Azure AD) to register an application and obtain the required Microsoft Graph API credentials.

Solution 1Recommended

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.

1
Register an Application in Microsoft Entra ID

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.

2
Assign Graph API Permissions

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.

3
Request an Access Token via VBA

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.

4
Download the Target File

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.

5
Connect ADODB to the Local Copy

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.

Authenticate via Microsoft Graph and Download to a Temporary Path
Developer Resources: Refer to the official Microsoft Graph API documentation for the exact HTTP endpoints to locate your SharePoint site ID, drive ID, and item ID.
Free Microsoft Office alternative

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. 1. Download and Install WPS Office: Visit the official WPS website, download the free installer, and complete the rapid setup process.
  2. 2. Open Your Spreadsheets: Launch WPS Spreadsheets and open any existing Excel workbook. Your formatting, data, and standard functions will remain perfectly intact.
  3. 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.
High compatibility with Microsoft Excel formats (.xlsx, .xls, .csv).Lightweight software architecture that runs smoothly on both modern and older devices.Familiar user interface requiring zero learning curve for traditional Excel users.Built-in cloud sync capabilities to easily share and collaborate on files.
microsoft office alternative - wps office

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.