If I want to use the open Workbook object to get the fullname of an Excel file after saving it, but that file has been synchronized to OneDrive, I get a "https" address instead of a local one, which other programs cannot interpret.
How do I get the local filename of a file like this?

Example:
Save a file to "C:\Users\user\OneDrive - Company\Documents".
OneDrive does its synchronization.
Querying Workbook.FullName now shows as "https://..."

Universal Solution & Meta-Analysis of All Solutions

TLDR:

• For the solution, skip to the section The Solutions

• For the meta-analysis, skip to the section Testing and comparison of solutions

Background

@Cristian Buse and I worked extensively on this problem after testing all other solutions available online and finding none of them universally accurate.

In the end, both of us created independent solutions:

• @Cristian Buse developed his solution as part of one of his excellent VBA Libraries, to be specific, the Library VBA-FileTools. This library also provides a bunch of other very useful functionalities.

• My solution comes in the form of a standalone function without any dependencies. This is useful if this problem occurs in a small project where no additional functionality is required. Because implementing the desired universal functionality is complex, it is very long and convoluted for a single procedure.

---

The Solutions

NOTES:

• Should you encounter any bugs with our solutions, please report them here or on GitHub! In that case, I recommend you use this solution in the meantime, as it is the next most accurate solution available.

Solution 1 - Library

Import this library: VBA-FileTools from GitHub into your project. Getting the local name of your workbook is then as easy as:

GetLocalPath(ThisWorkbook.FullName)

^{Notes:
Full Mac support was added to this solution on Apr 5, 2023.
Support for OneDrive version 23.184.0903.0001 was added to this solution on Sep 25, 2023.}

Solution 2 - Standalone Function

Copy this function, from GitHub Gist into any standard code module.

Getting the local name of your workbook now works in the same way as with Solution 1:

GetLocalPath(ThisWorkbook.FullName)

^{Notes:
Partial Mac support was added to this solution on Dec 20, 2022, and full support on Mar 20, 2023.
Support for OneDrive version 23.184.0903.0001 was added to this solution on Oct 2, 2023.
This function also offers some optional parameters, but they should almost never be needed. (See Gist for more information)}

Unfortunately, providing the function directly in this answer is impossible because of its excessive length, and StackOverflows 30,000-character answer limit.

---

How Do the Solutions Work?

Both solutions get all of the required information for translating the OneDrive URL to a local path from the OneDrive settings files inside of the directory %localappdata%\Microsoft\OneDrive\settings\....

The following files may be read:

(Wildcards: * - zero or more characters; ? - one character)

????????????????.dat
????????????????.ini
global.ini
GroupFolders.ini
????????-????-????-????-????????????.dat
????????-????-????-????-????????????.ini
ClientPolicy*.ini
SyncEngineDatabase.db

Data from all of these files is used, to create a "dictionary" of all the local mount points on your pc, and their corresponding OneDrive URL-root. For example, for your personal OneDrive, such a local mount point could look like this: C:\Users\Username\OneDrive, and the corresponding URL-root could look like this: https://d.docs.live.net/f9d8c1184686d493.

For more information on how exactly the dictionary is built and used, please refer to the extensive comments above the code in the Gist of the standalone function and the resources linked there.

---

Testing and Comparison of Solutions

I conducted extensive testing of all the solutions I could find online. A selection of these tests will be presented here.

This is a list of some of the tested solutions:

Each line in the table in the below image represents one solution in the above table and they can be correlated using the solution number.
Likewise, each column represents a test case, they can be correlated to this test-table by using the test-number. Unfortunately, Stack Overflow doesn't allow answers long enough to include the table of test cases directly in this post.

^{All of this testing was done on Windows. On macOS, every solution except for Nr 32 and Nr 33 would pass 0/46 tests. The solutions presented in this post (#32 and #33) also pass every test on macOS.}

Most solutions pass very few tests. Many of these tests are relatively difficult to solve, some are absolute edge cases, such as tests Nr 41 to 46, that test how a solution deals with OneDrive folders that are synced to multiple different local paths, which can only happen if multiple Business OneDrive accounts are logged in on the same PC and even then needs some special setup. (More information on that can be found here in Thread 2)

Test Nr 22 contains various Unicode emoji characters in some folder names, this is why many solutions fail with error here.

If you have another different solution you would like me to test, let me know and I'll add it to this section.