VBA & Macro Problems

How to List SharePoint Folder Filenames Using Excel VBA

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to retrieve a list of filenames and their full paths from a SharePoint Online folder and write these details into an Excel worksheet using VBA.

Product: Excel, SharePoint Online
Device & OS: not provided
Scenario: Extracting file directories and metadata from a cloud-based SharePoint folder into a spreadsheet for tracking, auditing, or organizational purposes.
Observed behavior: The user needs a reliable VBA script or method to connect to SharePoint, extract the requested file metadata (name and path), and populate the worksheet without errors.

Before you start

Ensure you have the correct permissions to access the target SharePoint Online folder and that your Excel application has macros enabled to run the VBA script.

Solution 1Recommended

Map SharePoint as a Network Drive and Use FileSystemObject

This is the most straightforward method, allowing Excel VBA to treat the SharePoint folder like a standard local directory.

By mapping the SharePoint library to a local drive letter, you can use the standard Windows FileSystemObject in VBA to loop through files and extract their names and paths.

Map the SharePoint Folder

Open Windows File Explorer, right-click 'This PC', select 'Map network drive', and paste your SharePoint document library URL to assign it a drive letter.

Open the Excel VBA Editor

Press ALT + F11 in Excel, insert a new Module, and declare the FileSystemObject to handle file extraction.

Write the VBA Script

Write a script to clear the target worksheet, add column headers for 'Filename' and 'Path', and use a recursive loop to read files from the mapped drive letter.

Disconnect the Drive

Add a line in your VBA code or manually disconnect the network drive in File Explorer once the extraction process is completed.

Drive Mapping Tip: You may need to check 'Connect using different credentials' or log in via Internet Explorer/Edge first to establish an authenticated session before mapping the drive.

Solution 2

Use the SharePoint REST API

If mapping a network drive is restricted by your organization, you can use HTTP requests to query the SharePoint REST API directly from VBA.

Run Your VBA Macros Seamlessly with WPS Office

WPS Office Spreadsheets provides robust support for VBA and macros, allowing you to run scripts using FileSystemObject efficiently. You can execute your directory listing macros just as you would in Microsoft Excel.

1. Download and Install WPS: Download WPS Office from the official website and complete the installation on your PC.
2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your .xlsm file containing the directory extraction script.
3. Enable Macros: Navigate to the 'Developer' tab on the ribbon, ensure Macros are enabled, and open the VBA Editor using ALT + F11.
4. Execute the Script: Run your macro to pull the mapped SharePoint folder filenames and paths directly into your WPS worksheet.

Fully compatible with Microsoft Excel (.xlsx, .xlsm, .xls) file formats.Built-in VBA editor allows you to write, edit, and execute FileSystemObject scripts.Lightweight installation that runs highly efficiently on older or slower devices.Free to use for everyday spreadsheet tasks and macro execution.

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I map a SharePoint folder as a network drive?

Mapping a SharePoint drive requires specific compatibility settings and valid session cookies. If your organization enforces strict security policies or disables legacy authentication, the mapping process may fail, requiring you to use the REST API instead.

Do I need special permissions to read SharePoint files via VBA?

Yes, your user account must have at least 'Read' access to the target SharePoint document library. When using the REST API, you may also need explicit API permissions configured in Azure AD.

Can I retrieve file properties like Date Modified alongside the filename?

Yes. Once you connect to the folder using FileSystemObject, you can access properties like DateLastModified, Size, and Type for each file and write them to your worksheet alongside the filename and path.

Does WPS Office support VBA for FileSystemObject operations?

Yes, WPS Office Spreadsheets fully supports VBA, including the Windows FileSystemObject, meaning macros designed to read mapped network drives will run seamlessly.

Excel & Data Problems