Forum Discussion

rerawa's avatar
rerawa
Copper Contributor
May 09, 2023

Thisworkbook.Fullname - determine local path with VBA

Hello, an Excel file saved in a OneDrive / Sharpoint folder returns the URL (https://xxxx-my.sharepoint ...) for Thisworkbook.fullname. There are various solutions on the net using the registry, En...
Macros and VBA
NikolinoDE's avatar
NikolinoDE
Gold Contributor
May 09, 2023

rerawa 

You are correct that using the Scripting.FileSystemObject is a good way to determine the local path of an Excel file.

Here's an example VBA code that uses the Scripting.FileSystemObject to get the local path:

Sub GetLocalPath()
    Dim fso As Object
    Dim localPath As String
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    localPath = fso.GetParentFolderName(ThisWorkbook.FullName)
    
    Debug.Print localPath
End Sub

This code uses the GetParentFolderName method of the FileSystemObject to get the local path of the Excel file.

There are other solutions available that involve using VBA libraries or standalone functions to determine the local path of a workbook saved on OneDrive or SharePoint.

These solutions can be more complex but may provide more accurate results.

 

As for your second question, unfortunately and as far i know there is no direct way provided by Microsoft to get the local path of an Excel file saved in OneDrive/SharePoint. Using the Scripting.FileSystemObject or other methods like the registry or environment variables is currently the most reliable way to get the local path in VBA.

 

Hope one of the suggested solutions will help you!

 

Resources