VBA & Macro Problems

How to Safely Create SharePoint Folders Using VBA in Excel

John Wilson Oct 12, 2026 869 views

Question details

The user needs to securely create folders in a SharePoint document library using a VBA macro without running into connection, routing, or security limitations common to shared environments.

How to Safely Create SharePoint Folders Using VBA

Product: Spreadsheets
Device & OS: not provided
Scenario: Automating folder creation in a centralized SharePoint library via local VBA macros, typically for team workflows like payroll or reporting.
Observed behavior: Standard VBA file-system commands fail natively on SharePoint web URLs. Directly integrating SharePoint with VBA introduces errors, necessitating a temporary network drive mapping workaround.

Before you start

Ensure you have the exact URL of your SharePoint document library and that your current Windows user account has 'Contribute' or 'Edit' permissions to create folders in that location.

Solution 1Recommended

Temporarily Map a SharePoint Library as a Network Drive

This method maps the SharePoint URL to a local drive letter, executes standard file-creation commands, and then immediately disconnects the drive to bypass security constraints.

Since VBA's native MkDir command does not support HTTP/HTTPS paths natively, you must trick the system into treating the SharePoint library as a local network path.

Using the WScript.Network object allows you to temporarily mount the library, safely create your folders using the FileSystemObject, and unmount it to prevent persistent connection errors in shared macros.

Open the VBA Editor

Open your workbook, navigate to the Developer tab, and click 'Visual Basic' (or press ALT + F11). Insert a new Module from the Insert menu.

Define Network and FileSystem Objects

Declare your variables and initialize the network object using `Set network = CreateObject("WScript.Network")`.

Map the SharePoint URL to a Drive Letter

Use the command `network.MapNetworkDrive "Z:", "https://your_sharepoint_site/sites/your_site/library"` to mount the site. Ensure you choose a drive letter (like Z:) that is not currently in use.

Check for and Create the Folder

Create a FileSystemObject (`CreateObject("Scripting.FileSystemObject")`). Use the `Dir()` function to check if the folder already exists, and if not, execute `fs.CreateFolder` using the mapped "Z:\" path.

Unmap the Network Drive

In your error-handling block and at the end of your script, strictly use `network.RemoveNetworkDrive "Z:", True, True` to disconnect the drive, returning the environment to its original state.

Temporarily Map a SharePoint Library as a Network Drive

Error Handling is Crucial: Always include `On Error Resume Next` before mapping the drive. This ensures that if the mapping fails due to network or permission issues, the script can catch the error, alert the user, and safely exit without crashing.

Automate with VBA in WPS Spreadsheet

WPS Office comes equipped with a powerful, built-in VBA editor that handles FileSystemObject and WScript commands perfectly. You can manage your SharePoint macros seamlessly while enjoying a lighter, highly compatible spreadsheet environment.

1. Open WPS Spreadsheet: Launch WPS Office and open the workbook where you want to automate folder creation.
2. Access the Developer Tools: Click on the 'Developer' tab on the top ribbon menu to access macro features.
3. Launch the VBA Editor: Click on the 'VBA Editor' button. Insert a new module and paste your drive-mapping script.
4. Run the Macro: Click 'Run' or press F5 to execute the script and securely create your SharePoint folders.

Fully compatible with Microsoft Excel VBA scripts and macrosRobust support for Windows Script Host and FileSystem operationsFree, lightweight, and incredibly fast to installFamiliar tabbed user interface for easy transition

microsoft office alternative - wps office

Frequently Asked Questions

Why does the standard VBA MkDir function fail with SharePoint URLs?

The native MkDir function in VBA expects a local file path or a standard UNC network path (e.g., \\server\share). It does not natively understand HTTPS web protocols used by SharePoint, which triggers a "Path not found" or "Bad file name" error.

Are there alternatives to drive mapping for creating SharePoint folders?

Yes. If your environment prohibits mapping network drives, you can use the SharePoint REST API or Microsoft Graph API via XMLHTTP requests in VBA. Alternatively, triggering a Microsoft Power Automate flow from your workbook is a modern cloud-based solution.

Should I hard-code my SharePoint credentials in the VBA script?

No, you should never hard-code credentials (username and password) in your VBA code, as it exposes them to anyone who accesses the macro. Rely on the user's active Windows authentication or existing session tokens when mapping the drive.

What happens if multiple users run the shared macro simultaneously?

If multiple users map the exact same drive letter simultaneously on their own local machines, it works fine because the mapping is local to their Windows session. However, you must ensure the script unmounts the drive perfectly to prevent locked sessions or ghost drives on their computers.

Excel & Data Problems