How to Use Dynamic Windows Usernames and SharePoint URLs in VBA Paths
Question details
The user needs to replace a hard-coded Windows username in a VBA OneDrive path with a dynamic profile path or a SharePoint HTTPS URL to ensure macros work across different computers.
- Product
- Excel / Word (VBA)
- Device & OS
- Windows
- Scenario
- Running a VBA macro that saves or opens files on a synchronized OneDrive or SharePoint folder shared across multiple users.
- Observed behavior
- Hard-coded local paths fail on different computers because the Windows username or local drive letter changes per user.
Ensure you have the exact name of your synced OneDrive folder or the correct root HTTPS URL for your SharePoint site before modifying your VBA code.
Use the USERPROFILE Environment Variable for Local Sync Paths
Retrieve the current user's profile path dynamically to avoid hard-coded Windows usernames in your VBA scripts.
This method is ideal when you and your colleagues use the local OneDrive desktop client to synchronize files to your hard drives. It automatically fetches the 'C:\Users\username' portion of the path based on who is currently logged into Windows.
In your VBA editor, create a variable to hold the user profile path using the Environ function: enviro = CStr(Environ("USERPROFILE")).
Append your specific OneDrive folder name to the variable. For example: sFolder = enviro & "\OneDrive - YourCompanyName\".
Use the sFolder variable in your file save or open operations, replacing any static strings like "C:\Users\JohnDoe\".
Use a SharePoint HTTPS Address for Cloud Files
Bypass local drive and username differences entirely by saving directly to the SharePoint or OneDrive cloud URL.
Try WPS Office for Seamless Macro and VBA Compatibility
If you frequently work with complex VBA macros, Excel add-ins, or shared documents, consider WPS Office. It provides robust support for standard VBA scripts while offering a lightweight, cost-effective solution.
- 1. Download WPS Office: Visit the official WPS Office website to download the free installer.
- 2. Install the software: Run the installer and launch WPS Spreadsheet on your computer.
- 3. Open your macro-enabled files: Open your existing .xlsm files; your VBA macros will run smoothly in the familiar interface.
Frequently Asked Questions
Why does my VBA macro fail when another user runs it from their OneDrive?
If your VBA macro uses a hard-coded path like C:\Users\JohnDoe\OneDrive, it will fail for other users because their Windows username (and therefore their local folder path) is different. Using the USERPROFILE environment variable solves this issue by dynamically fetching the correct user path.
How do I find my exact SharePoint HTTPS URL for VBA?
Open your SharePoint folder in a web browser and copy the URL from the address bar. You will usually need to strip away any web-view parameters (like ?web=1) so the path ends cleanly with the actual folder or file name.
Does the Environ function work on Mac?
No, the Environ("USERPROFILE") method is specific to the Windows operating system. If you are writing VBA for Mac, you will need to use Mac-specific AppleScript commands or Mac environment equivalents to retrieve the user's home directory.
Can I use relative paths instead of the USERPROFILE variable?
If your macro is saving a file in the same directory as the currently active workbook, you can use ThisWorkbook.Path to get the current directory dynamically. This often bypasses the need to build the Windows user path from scratch.
