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!

[#3]

@rerawa 

The only current reply by @NikolinoDE contains some misleading information.

There is no way to get the local path of a OneDrive/Sharpoint synchronized Excel file by using a Scripting.FileSystemObject in VBA.

Addressing your first question

The code you provided, 

localFullFileName = fso.GetAbsolutePathName(Application.ActiveWorkbook.Name)

 does nothing else than append whatever string the expression in the bracket returns, in your case the name of the active workbook, to the path the current shell environment is set to, which is the path returned by the CurDir function. This means, your code is equivalent to 

localFullFileName = CurDir & ActiveWorkbook.Name

Unfortunately, CurDir is NOT meant to return the path of the active workbook and will not always do so. More information on this can be found here.

The code in @NikolinoDE's answer also does nothing to localize a path returned by VBA in the case of a OneDrive/SharePoint synchronized file. It only removes the file name, so the last part of the link returned by ThisWorkbook.FullName. It is therefore equivalent to 

ThisWorkbook.Path

which will still return a URL in the OneDrive/SharePoint case.

Regarding your second question

It is possible to get the local path using other means, and this StackOverflow answer describes the best option for how this is currently possible, however, it is not what I would describe as a more direct way from Microsoft.

There is indeed a more direct way from Microsoft and it is described in this other StackOverflow answer. Still, I would strongly discourage anyone from using it as it relies on UI automation and is much less reliable than the first answer I linked. 

[#4]

@rerawa 

Resolve the problem of ThisWorkbook.Path returning a URL in OneDrive

If you want to get local path of ThisWorkbook in OneDrive and OneDrive for Buisness instead of URL, please refer to the following GitHub. It provides three deferent solutions:
https://github.com/Excel-VBA-Diary/GetThisWorkbookLocalPath

[#5]

thank so much, i'm looking for this.