VBA & Macro Problems

How to Get the Local OneDrive or SharePoint Path with Excel VBA

Steven Parker Nov 13, 2026 869 views

Question details

The user needs an Excel VBA method to obtain the local synchronized folder path of a workbook stored in OneDrive or SharePoint when VBA returns a URL instead of a Windows path.

Get the Local OneDrive or SharePoint Path with Excel VBA

Product: WPS Office Spreadsheets
Device & OS: not provided
Scenario: A macro-enabled workbook is saved in a synced OneDrive or SharePoint folder, and VBA code must use the local path for file operations.
Observed behavior: FileSystemObject and ThisWorkbook.Path return a OneDrive or SharePoint https URL, or GetAbsolutePathName returns the current directory instead of the workbook location.

Before you start

Open the VBA editor with Alt+F11 and run a small test such as Debug.Print ThisWorkbook.FullName to see whether the workbook is exposed as a local path like C:\Users\... or as an https OneDrive/SharePoint URL. Use ThisWorkbook, not ActiveWorkbook, so the result always refers to the workbook that contains the macro.

Solution 1

Confirm whether the workbook path is already local or is a OneDrive/SharePoint URL

Use this first to avoid trying to convert a URL with FileSystemObject when the workbook path is already a valid local path.

FileSystemObject is safe when ThisWorkbook.FullName already contains a drive-letter path such as C:\Users\yourname\OneDrive\Documents\file.xlsm. It cannot translate an https URL into that synchronized location.

Open the VBA editor

In WPS Spreadsheets or Excel, press Alt+F11 to open the VBA editor.

Inspect the workbook path

Insert a standard module and run: Sub ShowWorkbookPath() Debug.Print ThisWorkbook.FullName End Sub Check the Immediate Window output.

Use the correct local-path method

If the output starts with a drive letter such as C:\, use FileSystemObject.GetParentFolderName(ThisWorkbook.FullName) or ThisWorkbook.Path. If it starts with https:// or contains OneDrive/SharePoint, continue to the next solution.

Why ThisWorkbook matters: ActiveWorkbook can change when the user switches workbooks, so the macro should reference ThisWorkbook to stay on the workbook that contains the code.

Solution 2Recommended

Resolve the OneDrive or SharePoint local synchronized path using the environment or registry method

Use this solution when ThisWorkbook.FullName or ThisWorkbook.Path returns a OneDrive or SharePoint URL and you need the synchronized local folder.

Solution 3

Use a documented URL-to-local mapping solution for SharePoint or OneDrive for Business

Keep this alternative when the environment/registry approach is not sufficient and you need a more direct mapping for a SharePoint document library URL.

WPS Spreadsheets VBA

Get a Local OneDrive or SharePoint Workbook Path with VBA in WPS Spreadsheets

Use WPS Spreadsheets' Excel-compatible VBA editor to run the same local path resolution code: check ThisWorkbook.FullName, then map the OneDrive or SharePoint URL to the local synchronized path with environment or registry methods.

1. Open WPS Spreadsheets: Launch WPS Office and open the macro-enabled workbook from the synchronized OneDrive or SharePoint local folder.
2. Open the VBA editor: Press Alt+F11 in WPS Spreadsheets to open the VBA/Macro editor.
3. Insert a module: Right-click the VBA project in the Project Explorer, choose Insert > Module, and paste the local path resolution function.
4. Run the macro: Call the function with ThisWorkbook.FullName, run the macro, and verify that it returns a local drive-letter path.

Runs Excel-compatible VBA macros for OneDrive and SharePoint local path resolutionOpens and saves Microsoft Excel .xlsx, .xlsm, and .xlsb worksheet formatsFree and lightweight Office suite with a familiar VBA/macro workflowUses ThisWorkbook safely so macros refer to the correct workbookAvoids paying for Microsoft Office when you only need the local path macro

microsoft office alternative - wps office

Frequently Asked Questions

Why does FileSystemObject return a URL instead of a local path for OneDrive files?

FileSystemObject only works with Windows filesystem paths. When ThisWorkbook.FullName is already a URL, it cannot convert the URL to the local synchronized folder; you must first map the URL through the OneDrive sync root.

Does GetAbsolutePathName(Application.ActiveWorkbook.Name) return the workbook location?

No. That statement appends the active workbook name to the current shell directory, effectively CurDir & ActiveWorkbook.Name, and does not identify the workbook's actual OneDrive or SharePoint location.

Should I use ActiveWorkbook or ThisWorkbook when referring to the macro workbook?

Use ThisWorkbook. ActiveWorkbook is whichever workbook is currently active and can change, while ThisWorkbook always refers to the workbook that stores the VBA code.

What should I do if ThisWorkbook.FullName already shows a local C:\ path?

You can use FileSystemObject.GetParentFolderName(ThisWorkbook.FullName) or ThisWorkbook.Path directly. No URL-to-local mapping is required when Excel or WPS already exposes a local path.

Can I run this local path resolution macro in WPS Spreadsheets?

Yes. WPS Spreadsheets on Windows supports VBA/macro editing through Alt+F11 and can run Excel-compatible code that reads environment variables or registry information for OneDrive and SharePoint.

Excel & Data Problems