Ce site est optimisé pour être consulté depuis un navigateur moderne dans lequel JavaScript est activé.

Excel's fullname property avec OneDrive

ayi

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://…”

ayi_2

<h2><a name="p-21804-universal-solution-meta-analysis-of-all-solutions-1" class="anchor" href="#p-21804-universal-solution-meta-analysis-of-all-solutions-1" aria-label="Heading link"></a>Universal Solution & Meta-Analysis of All Solutions</h2>
TLDR:
<ul>
<li></li>
</ul>
For the solution, skip to the section The Solutions
<ul>
<li></li>
</ul>
For the meta-analysis, skip to the section Testing and comparison of solutions
<h2><a name="p-21804-background-2" class="anchor" href="#p-21804-background-2" aria-label="Heading link"></a>Background</h2>
@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:
<ul>
<li></li>
</ul>
@Cristian Buse developed his solution as part of one of his excellent VBA Libraries, to be specific, the Library <code>VBA-FileTools</code>. This library also provides a bunch of other very useful functionalities.
<ul>
<li></li>
</ul>
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.
<h2><a name="p-21804-the-solutions-3" class="anchor" href="#p-21804-the-solutions-3" aria-label="Heading link"></a>The Solutions</h2>
NOTES:
<ul>
<li>Should you encounter any bugs with our solutions, please report them here or on GitHub! In that case, I recommend you use <a href="https://stackoverflow.com/a/72736924/12287457">this solution</a> in the meantime, as it is the next most accurate solution available.</li>
</ul>
<h2><a name="p-21804-solution-1-library-4" class="anchor" href="#p-21804-solution-1-library-4" aria-label="Heading link"></a>Solution 1 - Library</h2>
Import this library: <a href="https://github.com/cristianbuse/VBA-FileTools">VBA-FileTools</a> from GitHub into your project. Getting the local name of your workbook is then as easy as: 
`GetLocalPath(ThisWorkbook.FullName)
<pre><code class="lang-auto">

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)

</code></pre>
<blockquote></blockquote>
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.
<h2><a name="p-21804-how-do-the-solutions-work-5" class="anchor" href="#p-21804-how-do-the-solutions-work-5" aria-label="Heading link"></a>How Do the Solutions Work?</h2>
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 <code>%localappdata%\Microsoft\OneDrive\settings...</code>.
The following files may be read:
(Wildcards: <code>*</code> - zero or more characters; <code>?</code> - one character) 
???.dat ???.ini global.ini GroupFolders.ini ???-???-???-???-???.dat ???-???-???-???-???.ini ClientPolicy*.ini SyncEngineDatabase.db <pre><code class="lang-auto"> 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:

Nr.
Author
Solution
Tests passed

1
Koen Rijnsent
https://stackoverflow.com/a/71753164/12287457
0/46

2
Cooz2, adapted for Excel by LucasHol
https://social.msdn.microsoft.com/Forums/office/en-US/1331519b-1dd1-4aa0-8f4f-0453e1647f57/how-to-get-physical-path-instead-of-url-onedrive
0/46

3
Julio Garcia
https://stackoverflow.com/a/74360506/12287457
0/46

4
Claude
https://stackoverflow.com/a/64657459/12287457
0/46

5
Variatus
https://stackoverflow.com/a/68568909/12287457
0/46

6
MatChrupczalski
https://social.msdn.microsoft.com/Forums/office/en-US/1331519b-1dd1-4aa0-8f4f-0453e1647f57/how-to-get-physical-path-instead-of-url-onedrive
1/46

7
Caio Silva
https://stackoverflow.com/a/67318424/12287457 and https://stackoverflow.com/a/67326133/12287457
2/46

8
Alain YARDIM
https://stackoverflow.com/a/65967886/12287457
2/46

9
tsdn
https://stackoverflow.com/a/56326922/12287457
2/46

10
Peter G. Schild
https://stackoverflow.com/a/60990170/12287457
2/46

11
TWMIC
https://stackoverflow.com/a/64591370/12287457
3/46

12
Horoman
https://stackoverflow.com/a/60921115/12287457
4/46

13
Philip Swannell
https://stackoverflow.com/a/54182663/12287457
4/46

14
RMK
https://stackoverflow.com/a/67697487/12287457
5/46

15
beerockxs
https://stackoverflow.com/a/67582367/12287457
5/46

16
Virtuoso
https://stackoverflow.com/a/33935405/12287457
5/46

17
COG
https://stackoverflow.com/a/51316641/12287457
5/46

18
mohnston
https://stackoverflow.com/a/68569925/12287457
5/46

19
Tomoaki Tsuruya (鶴谷朋亮)
https://tsurutoro.com/vba-trouble2/
5/46

20
Greedo
https://gist.github.com/Greedquest/ 52eaccd25814b84cc62cbeab9574d7a3
6/45

21
Christoph Ackermann
https://stackoverflow.com/a/62742852/12287457
6/46

22
Schoentalegg
https://stackoverflow.com/a/57040668/12287457
6/46

23
Erlandsen Data Consulting
https://www.erlandsendata.no/?t=vbatips&p=4079
7/46

24
Kurobako (黒箱)
https://kuroihako.com/vba/onedriveurltolocalpath/
7/46

25
Tim Williams
https://stackoverflow.com/a/70610729/12287457
8/46

26
Erik van der Neut
https://stackoverflow.com/a/72709568/12287457
8/46

27
Ricardo Diaz
https://stackoverflow.com/a/65605893/12287457
9/46

28
Iksi
https://stackoverflow.com/a/68963896/12287457
11/46

29
Gustav Brock, Cactus Data ApS
https://stackoverflow.com/a/70521246/12287457
11/46

30
Ricardo Gerbaudo
https://stackoverflow.com/a/69929678/12287457
14/46

31
Guido Witt-Dörring Short solution
https://stackoverflow.com/a/72736924/12287457
24/46

32
Ion Cristian Buse
https://github.com/cristianbuse/VBA-FileTools
46/46

33
Guido Witt-Dörring Universal Solution
https://gist.github.com/guwidoe/ 038398b6be1b16c458365716a921814d
46/46

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.</code></pre>