Excel Full Name Property with OneDrive

Question

If I want to use the open Workbook object to get the full name of the Excel file after saving it, but this file was synchronized with OneDrive, I get the address "https" instead of the local one, which other programs cannot interpret.
How to get a local file name of this type?

Example:
Save the file to the folder "C: \ Users \ user \ OneDrive - Company \ Documents".
OneDrive performs its synchronization.
The request Workbook.FullName now displays as "https: // ..."

Answer 1

You can improve Virtuoso’s response to reduce (although not eliminate) the likelihood that the function will return the “wrong” file location. The problem is that there are different URLs, .FullName may be a book .FullName . Here are three that I know of:

1. OneDrive User Associated URL
2. OneDrive for Business User Associated URL
3. The URL associated with someone else on OneDrive, in case this other person has “shared” the file (in this case, you open the file through “File”> “Open”> “Share with me”)

On my PC, I can get the appropriate local folders to map the first two URLs through the OneDriveConsumer and OneDriveCommercial environment variables that exist in addition to the OneDrive environment variable, so the code below uses them. I do not know if it is possible to process the "Shared with Me" files, and the code below will return their location https:// -style.

 Private Function Local_Workbook_Name(ByRef wb As Workbook) As String Dim i As Long, j As Long Dim OneDrivePath As String Dim ShortName As String 'Check if it looks like a OneDrive location If InStr(1, wb.FullName, "https://", vbTextCompare) > 0 Then 'Replace forward slashes with back slashes ShortName = Replace(wb.FullName, "/", "\") 'Remove the first four backslashes For i = 1 To 4 ShortName = Mid(ShortName, InStr(ShortName, "\") + 1) Next 'Loop to see if the tentative LocalWorkbookName is the name of a file that actually exists, if so return the name For j = 1 To 3 OneDrivePath = Environ(Choose(j, "OneDrive", "OneDriveCommercial", "OneDriveConsumer")) If Len(OneDrivePath) > 0 Then Local_Workbook_Name = OneDrivePath & "\" & ShortName If Dir(Local_Workbook_Name) <> "" Then Exit Function End If End If Next j 'Possibly raise an error here when attempt to convert to a local file name fails - eg for "shared with me" files End If Local_Workbook_Name = wb.FullName End Function 

Unfortunately, if the files exist with the same paths both in the OneDrive folder and in the OneDrive for business folder, the code will not be able to distinguish between them and may return “incorrect”. I have no solution for this.

Answer 2

I found a stream on the network that contains enough information to put together something simple to solve this problem. I really implemented the solution in Ruby, but this is a version of VBA:

 Option Explicit Private Function Local_Workbook_Name(ByRef wb As Workbook) As String Dim Ctr As Long Dim objShell As Object Dim UserProfilePath As String 'Check if it looks like a OneDrive location If InStr(1, wb.FullName, "https://", vbTextCompare) > 0 Then 'Replace forward slashes with back slashes Local_Workbook_Name = Replace(wb.FullName, "/", "\") 'Get environment path using vbscript Set objShell = CreateObject("WScript.Shell") UserProfilePath = objShell.ExpandEnvironmentStrings("%UserProfile%") 'Trim OneDrive designators For Ctr = 1 To 4 Local_Workbook_Name = Mid(Local_Workbook_Name, InStr(Local_Workbook_Name, "\") + 1) Next 'Construct the name Local_Workbook_Name = UserProfilePath & "\OneDrive\" & Local_Workbook_Name Else Local_Workbook_Name = wb.FullName End If End Function Private Sub testy() MsgBox ActiveWorkbook.FullName & vbCrLf & Local_Workbook_Name(ActiveWorkbook) End Sub 

Answer 3

Very helpful, thanks. I had a similar problem, but with the folder name, not the file name. Therefore, I changed it a little. I made this work for folder names and file names (it doesn't have to be a workbook). In case this is useful, the code is below:

 Public Function Local_Name(theName As String) As String Dim i As Integer Dim objShell As Object Dim UserProfilePath As String ' Check if it looks like a OneDrive location. If InStr(1, theName, "https://", vbTextCompare) > 0 Then ' Replace forward slashes with back slashes. Local_Name = Replace(theName, "/", "\") 'Get environment path using vbscript. Set objShell = CreateObject("WScript.Shell") UserProfilePath = objShell.ExpandEnvironmentStrings("%UserProfile%") ' Trim OneDrive designators. For i = 1 To 4 Local_Name = Mid(Local_Name, InStr(Local_Name, "\") + 1) Next i ' Construct the name. Local_Name = UserProfilePath & "\OneDrive\" & Local_Name Else ' (must already be local). Local_Name = theName End If End Function 

Answer 4

I have the same problem as you. But I solved this problem. First I turn off OneDrive before running the script.

You can add this script to the first script in your vba / module:

 Call Shell("cmd.exe /S /C" & "%LOCALAPPDATA%\Microsoft\OneDrive\OneDrive.exe /shutdown") 

and then, in your last script on your VBA / module, you can paste this to activate OneDrive:

 Call Shell("cmd.exe /S /C" & "start %LOCALAPPDATA%\Microsoft\OneDrive\OneDrive.exe /background") 

I am using Windows10 on this script.

Answer 5

Easy Fix (early 2019) - for everyone who has this problem:

OneDrive> Settings> Office: - uncheck "Use Office applications to sync Office files that I open"

This allows Excel to save the file in the typical file format "C: \ Users [username] \ OneDrive ..." instead of the UNC format "https: \".

Answer 6

Here's a slight improvement over Philip Swannell’s improvement on Virtuoso’s original answer, when the number "\" to remove from the path is greater than 4 / changes (depending on the file, I found that I need to remove 5 or sometimes 6 of them), The disadvantages mentioned by Philippe still exist.

 Private Function Local_Workbook_Name(ByRef wb As Workbook) As String 'returns local wb path or nothing if local path not found Dim i As Long Dim OneDrivePath As String Dim ShortName As String Dim testWbkPath As String Dim OneDrivePathFound As Boolean 'Check if it looks like a OneDrive location If InStr(1, wb.FullName, "https://", vbTextCompare) > 0 Then 'Replace forward slashes with back slashes ShortName = Replace(wb.FullName, "/", "\") 'Remove the first four backslashes For i = 1 To 4 ShortName = RemoveTopFolderFromPath(ShortName) Next 'loop through three OneDrive options For i = 1 To 3 OneDrivePath = Environ(Choose(i, "OneDrive", "OneDriveCommercial", "OneDriveConsumer")) If Len(OneDrivePath) > 0 Then 'Loop to see if the tentative LocalWorkbookName is the name of a file that actually exists, if so return the name Do While ShortName Like "*\*" testWbkPath = OneDrivePath & "\" & ShortName If Not (Dir(testWbkPath)) = vbNullString Then OneDrivePathFound = True Exit Do End If 'remove top folder in path ShortName = RemoveTopFolderFromPath(ShortName) Loop End If If OneDrivePathFound Then Exit For Next i Else Local_Workbook_Name = wb.FullName End If If OneDrivePathFound Then Local_Workbook_Name = testWbkPath End Function Function RemoveTopFolderFromPath(ByVal ShortName As String) As String RemoveTopFolderFromPath = Mid(ShortName, InStr(ShortName, "\") + 1) End Function 

Answer 7

I assume that there is a small error in the JK2017 code: "ShortName" -variable needs to be rebuilt each time these 3 versions of OneDrive are launched. So, ist should be inside the loop "For i = 1 To 3". I also added an option to get only the path instead of the full file name.

 Private Function Local_Workbook_Name(ByRef wb As Workbook, Optional bPathOnly As Boolean = False) As String 'returns local wb path or nothing if local path not found Dim i As Long, x As Long Dim OneDrivePath As String Dim ShortName As String Dim testWbkPath As String Dim OneDrivePathFound As Boolean 'Check if it looks like a OneDrive location If InStr(1, wb.FullName, "https://", vbTextCompare) > 0 Then 'loop through three OneDrive options For i = 1 To 3 'Replace forward slashes with back slashes ShortName = Replace(wb.FullName, "/", "\") 'Remove the first four backslashes For x = 1 To 4 ShortName = RemoveTopFolderFromPath(ShortName) Next 'Choose the version of Onedrive OneDrivePath = Environ(Choose(i, "OneDrive", "OneDriveCommercial", "OneDriveConsumer")) If Len(OneDrivePath) > 0 Then 'Loop to see if the tentative LocalWorkbookName is the name of a file that actually exists, if so return the name Do While ShortName Like "*\*" testWbkPath = OneDrivePath & "\" & ShortName If Not (Dir(testWbkPath)) = vbNullString Then OneDrivePathFound = True Exit Do End If 'remove top folder in path ShortName = RemoveTopFolderFromPath(ShortName) Loop End If If OneDrivePathFound Then Exit For Next i Else If bPathOnly Then Local_Workbook_Name = RemoveFileNameFromPath(wb.FullName) Else Local_Workbook_Name = wb.FullName End If End If If OneDrivePathFound Then If bPathOnly Then Local_Workbook_Name = RemoveFileNameFromPath(testWbkPath) Else Local_Workbook_Name = testWbkPath End If End If End Function Function RemoveTopFolderFromPath(ByVal ShortName As String) As String RemoveTopFolderFromPath = Mid(ShortName, InStr(ShortName, "\") + 1) End Function Function RemoveFileNameFromPath(ByVal ShortName As String) As String RemoveFileNameFromPath = Mid(ShortName, 1, Len(ShortName) - InStr(StrReverse(ShortName), "\")) End Function