Thisworkbook.Fullname - determine local path with VBA

[rerawa]

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, Enviroments ...
This seems to me to be too complex or too insecure.
1. what is wrong with using Set fso = CreateObject("Scripting.FileSystemObject")
localFullFileName = fso.GetAbsolutePathName(Application.ActiveWorkbook.Name)?
2) Isn't there a better, more direct way from Microsoft? Excel knows the local path (ALT + D + I + L)?
Greetings
Ralf

[#2]

@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!