The OneDrive Nightmare Continues: ThisWorkbook.Path and ThisWorkbook.FullName

Kevin Jones 7,265 Volunteer Moderator

I posted here about my OneDrive backup everything nightmare. Another bad dream has reared it's ugliness in VBA land: ThisWorkbook.Path and ThisWorkbook.FullName.

For some reason I absolutely cannot fathom, Excel returns a useless URL when reading these two properties in a workbook opened from any OneDrive or Sharepoint shared location. For example, ThisWorkbook.FullName value when opened from any location other than a OneDrive shared folder:

C:\Users\Public\Just Another Workbook.xlsx

But, put that workbook in any OneDrive shared folder, and this is returned:

https://datapros-my.sharepoint.com/personal/kjones\_dataautopros\_com/Documents/Just Another Workbook.xlsx

where the actual local path is:

C:\Users\Kevin Jones\OneDrive - Data Automation Professionals\Just Another Workbook.xlsx

Right off the bat it's painfully obvious that the URL is absolutely useless to anyone and everyone. One of the most common automation tasks that use ThisWorkbook.FullName or ThisWorkbook.Path is to navigate around inside the folder in which the workbook is located. There is no easy way to translate that URL into a local path. It is possible but it's a tricky operation - more on that later. What else is that URL good for? I have no idea.

The whole idea of sharing folders and files on a local drive is so that they can be used the same way as if they were in a non-shared folder. So we don't have to go to the online location and either download the file or open it in a browser. DropBox did it right. Google Drive did it right. So what were the Microsoft engineers thinking by exposing the URL versus the local path? Anyone have a clue?

A lot of people have said to turn off this OneDrive option. Even if that did work, it is no longer available in the current OneDrive UI/UX.

People have written URL parsers and file search routines to try to translate the URL into a local path. This StackOverflow thread got pretty crazy with ideas but none of the solutions worked in all scenarios. Remember that OneDrive supports one personal account, up to nine business accounts, and Sharepoint folders - each of these have different structures in their URLs without any obvious mapping to the folder on the local drive. I did find one possible solution in the StackOverflow thread that showed promise - I reworked it so that it worked in as many scenarios as I could find. I'm posting it below.

But, before we get to that, a message to Microsoft: Please refrain from serving up URLs in ThisWorkbook.FullName and ThisWorkbook.Path when the workbook is stored or opened on a local drive. It serves no purpose other than to annoy us and make our lives more difficult. It's also not reality as the workbook is, in fact, located on the local drive in a local folder - that's from where Excel opens it and saves it. Keep the URL an internal thing (I realize it's needed for real time collaboration) and, if you actually believe that someone out here can use it, add a new ThisWorkbook property such as SharedURL.

Below is the routine for getting the local path as it should be returned in FullName. It's been tested on multiple machines, Windows 10 and 11, and Sharepoint and OneDrive shared folders. Post to this thread if you make any improvements or fix any issues.

Public Function OneDriveLocalFilePath( _

Optional ByRef OneDriveFilePath As String _

) As String

' Returns the local file path given a URL to a file stored in a OneDrive or Sharepoint folder. For

' some reason the Excel Workbook properties Path and FullName return URLs instead of local paths.

' OneDriveFilePath - Any valid local path or URL referencing a OneDrive file. If the path cannot be

' resolved, the original path is returned.

Dim WScript As Object

Dim WinMgmtS As Object

Dim Result As String

Dim ProposedFilePath As String

Dim ConfirmedFilePath As String

Dim RegistryKey As Variant

Dim RegistryKeys As Variant

Dim Types As Variant

Dim CID As String

Dim MountPoint As String

Dim URLNamespace As String

Dim Path1 As String

Dim Path2 As String

Dim Directories As Variant

Dim ParentDirectory As String

' Default to the full name property of ThisWorkbook

If Len(OneDriveFilePath) = 0 Then

OneDriveFilePath = ThisWorkbook.FullName

End If

' Deterimine if the path is a URL or a local path

If Left(OneDriveFilePath, 8) = "https://" Then

' WScript and Winmgmts are used to navigate the registry

Set WScript = CreateObject("WScript.Shell")

Set WinMgmtS = GetObject("Winmgmts:root\default:StdRegProv")

' Enumerate the key HKEY_CURRENT_USER\SOFTWARE\SyncEngines\Providers\OneDrive

If WinMgmtS.EnumKey(&H80000001, "SOFTWARE\SyncEngines\Providers\OneDrive", RegistryKeys, Types) = 0 Then

For Each RegistryKey In RegistryKeys

' Each key has three interesting values:

' CID - Some hash code sometimes used in the path

' URLNameSpace - The URL to a parent directory in the cloud

' MountPoint - The local path OneDrive uses to mirror files found in the URLNameSpace address

CID = vbNullString

MountPoint = vbNullString

URLNamespace = vbNullString

ProposedFilePath = vbNullString

ConfirmedFilePath = vbNullString

On Error Resume Next

CID = WScript.RegRead("HKEY_CURRENT_USER\SOFTWARE\SyncEngines\Providers\OneDrive" & RegistryKey & "\CID")

MountPoint = WScript.RegRead("HKEY_CURRENT_USER\SOFTWARE\SyncEngines\Providers\OneDrive" & RegistryKey & "\MountPoint")

URLNamespace = WScript.RegRead("HKEY_CURRENT_USER\SOFTWARE\SyncEngines\Providers\OneDrive" & RegistryKey & "\URLNamespace")

On Error GoTo 0

' It's not always clear how for down the folder tree the URL and the mount point go so the file's parent is

            ' pulled from the OneDrivePath so that it can be compared with and without

Directories = Split(OneDriveFilePath, "/")

ParentDirectory = Directories(UBound(Directories) - 1)

' Remove any trailing slash from the URL name space

If Right(URLNamespace, 1) = "/" Then

URLNamespace = Left(URLNamespace, Len(URLNamespace) - 1)

End If

' Build two paths to test against: one without the CID and one with the CID

Path1 = URLNamespace & "/"

Path2 = URLNamespace & "/" & CID & "/"

' Try the path without the CID

If Left(OneDriveFilePath, Len(Path1)) = Path1 Then

' Try building the final local path from the mount point path and the unmatched end of the OneDrive path

                ' and return it if the file exists

ProposedFilePath = MountPoint & "" & Replace(Replace(Mid(OneDriveFilePath, Len(Path1) + 1), "/", ""), "%20", Space(1))

If ExistingFile(ProposedFilePath) Then

ConfirmedFilePath = ProposedFilePath

Exit For

End If

' Try building the final local path from the mount point path and the unmatched end of the OneDrive path

                ' but without the first folder and return it if the file exists

If Right(MountPoint, Len(ParentDirectory)) = ParentDirectory Then

ProposedFilePath = Replace(Replace(Mid(OneDriveFilePath, Len(Path1) + 1), "/", ""), "%20", Space(1))

ProposedFilePath = Mid(ProposedFilePath, InStr(ProposedFilePath, "") + 1)

ProposedFilePath = MountPoint & "" & ProposedFilePath

If ExistingFile(ProposedFilePath) Then

ConfirmedFilePath = ProposedFilePath

Exit For

End If

' Try building the final local path from the mount point path with the CID attached and the unmatched end

            ' of the OneDrive path and return it if the file exists

If Left(OneDriveFilePath, Len(Path2)) = Path2 Then

ProposedFilePath = Replace(Replace(Mid(OneDriveFilePath, Len(Path2)), "/", ""), "%20", Space(1))

ProposedFilePath = Mid(ProposedFilePath, InStr(ProposedFilePath, "") + 1)

ProposedFilePath = MountPoint & "" & ProposedFilePath

If ExistingFile(ProposedFilePath) Then

ConfirmedFilePath = ProposedFilePath

Exit For

End If

Next RegistryKey

End If

' Return the confirmed file path if a valid path was found

If Len(ConfirmedFilePath) > 0 Then

Result = ConfirmedFilePath

Else

Result = OneDriveFilePath

End If

Else

' The path is not a URL so return it as-is

Result = OneDriveFilePath

End If

OneDriveLocalFilePath = Result

End Function

Public Function ExistingFile( _

ByVal FilePath As String _

) As Boolean

' Returns True if the file exists, False otherwise. This routine does not use the Dir technique as

' the Dir function resets any current Dir process.

' FilePath - Full path to the folder or file to be evaluated.

Dim Attributes As Long

On Error Resume Next

Attributes = GetAttr(FilePath)

ExistingFile = (Err.Number = 0) And (Attributes And vbDirectory) = 0

Err.Clear

End Function

Kevin

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

105 answers

Anonymous

2024-02-14T11:32:03+00:00

Thank you for advice and hope that I'm not asking too much.

This is the link to where I made "safe" copies of the files and folder structure https://wastetechservices-my.sharepoint.com/:f:/p/ea/EsAv9JnbfSdJuLcePh7e0fUBB5aBP4gV28F5HcUYWDjzpA?e=DMQw7h

There is a README file that I hope describes the process and a bit about me and my limited experience.
Was this answer helpful?

0 comments No comments
Kevin Jones 7,265 Reputation points Volunteer Moderator

2024-02-14T00:49:29+00:00

The VBE/VBA environment is the best debugging environment there is for VBA. So nothing to recommend there.

As far as resources at a reasonable rate, you are pretty much there. There are a lot of Excel and VBA experts hanging out on expert forums such as this one and Experts Exchange. They like helping people like you solve problems and they do it for free! We have egos and we are competitive - we like sharing our experience and impressing our peers and regular users like you. It's just the way the technology world works.

On to your problem...

Without divulging any confidential information, please show the paths to the workbook (local directory) and the source file in the "different" directory. Also the directory to which you are copying these files.

What VBA commands or utility are you using to copy the file? Are you using any error handling such as On Error Resume Next?

Do you know how to use the VBA debugger? The Immediate window? The Locals window? Setting a breakpoint and stepping through code? Using the Watch window to watch variables?

Kevin
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments
Anonymous

2024-02-13T22:14:48+00:00

Kevin, I'm having a weird problem and with my lack of any real VBA experience, I'm pulling my hair out.

When I first started the project all the files were in the local directory and everything worked perfectly. Then the user tells me that isn't what they want, they want one of the files that I copy from to be in a different directory. Now I can't get the files to copy. From what I can see both files open, but the copy fails but without any errors.

I have asked you for more than enough and you have been amazing, and I can't ask you for anymore. So, my question is, is there some website that I can go to that has a better debugger than the VBA editor? Or is there some resource that I can work with at a reasonable rate as this is coming out of my pocket?

Thanks
Was this answer helpful?

0 comments No comments
Anonymous

2024-02-09T19:25:07+00:00

When I do the next version of my code I will incorporate your new function. Thank you again.
Was this answer helpful?

0 comments No comments
Kevin Jones 7,265 Reputation points Volunteer Moderator

2024-02-09T18:16:07+00:00
This is a routine that I am using extensively at the moment with a client that works exclusively in the OneDrive world. And I like my library of routines to be as robust and bulletproof as possible so anything we do here in this thread is important.

I'm not sure how your last use case is an issue for the routine. Once we have translated the ThisWorkbook.FullName or ThisWorkbook.Path to get the local path for the workbook, everything after that will be in the local path format. Any dialogs displayed asking the user for the location of folders or files will always return the local path, not the shared URL path. Asking the user for a file name and checking for it's existence is as simple as:

If ExistingFile(LocalPath & "" & FileName) Then

Remember that this routine's sole purpose is to translate the badly implemented FullName and Path properties of ThisWorkbook into a local path so that we can navigate around our local world. Everything else uses local paths.

Assuming you are asking for file name in the same folder as the workbook, here is an implementation of the above:

Public Function QueryForFile() As String
```
Dim FileName As String 

Dim ThisWorkbookPath As String 

Dim Result As String 

ThisWorkbookPath = OneDriveLocalFilePath(ReturnFolderPathOnly:=True) 

Do 

    Result = Application.InputBox("Enter the file name to process:", "Enter File Name To Process", "", Type:=2) 

    If Result = "False" Then Exit Do 

    If ExistingFile(ThisWorkbookPath & "\" & Result) Then 

        QueryForFile = Result 

        Exit Do 

    Else 

        MsgBox "The file name entered, '" & Result & "', could not be found.", vbExclamation + vbOKOnly 

        Result = vbNullString 

    End If 

Loop While Len(Result) = 0 
```
End Function

The function will return an existing file name or an empty string if the user gives up. It will keep asking the user for a file name until they either enter one that exists or they give up.

Even better is to use Application.GetOpenFilename which lets the user choose from all existing files...

The function accepts five optional parameters which are described below.

FileFilter - A string specifying file filtering criteria. The string can contain any number of criteria seperated with commas where each criterion consists of two elements: a readable description and an MS-DOS file filter description. The readable description does not have any syntax requirements. The second element is an MS-DOS file filter specification. Some examples:
```
"Text Files (\*.txt), \*.txt" 

"All Files (\*.\*),\*.\*" 

"Microsoft Excel Workbook (\*.xls), \*.xls" 
```
If omitted, this argument defaults to "All Files (*.*),*.*".

FilterIndex - Specifies the index number of the default file filtering criteria, from 1 to the number of filters specified in FileFilter. If this argument is omitted or greater than the number of filters present, the first file filter is used.

Title - Specifies the title of the dialog box. If this argument is omitted, the default title is used which is "Open".

ButtonText - The text in the command button. Valid only on the Macintosh.

MultiSelect - True to allow multiple file names to be selected. False to allow only one file name to be selected. The default value is False.

The GetOpenFilename returns a full path to the selected file or False is Cancel was clicked. If assigned to a variant the variant is changed to a Boolean data type and set to False. The easiest way to use the GetOpenFilename function is to assign it to a variant and then test for the variable type as illustrated with the sample code below.

Dim FilePath As Variant

FilePath = Application.GetOpenFilename("Microsoft Excel Workbook (*.xls), *.xls")

If VarType(FilePath) = vbBoolean Then
```
  MsgBox "Dialog cancelled." 
```
Else
```
  ' Do something with the file path 
```
End If

Kevin
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments