Microsoft 365 and Office | Excel | For business | Windows
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
I fixed a minor issue in the code. Here is all of it:
Public Function OneDriveLocalFilePath( _
Optional ByRef OneDriveFilePath As String, _
Optional ByVal ReturnFolderPathOnly As Boolean, _
Optional ByVal ReturnEmptyIfFileNotFound As Boolean _
) 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.
'
' ReturnFolderPathOnly - Pass True to return the folder path only. This is the equivelant of
' ThisWorkbook.Path. Optional. If omitted then False is assumed.
'
' ReturnEmptyIfFileNotFound - Pass True to return an empty or null string if the file cannot be
' found, False to return an error message. Optional. If omitted then False is assumed.
Const RegistryPath As String = "HKEY_CURRENT_USER\SOFTWARE\SyncEngines\Providers\OneDrive\"
Dim Calculation As XlCalculation
Dim ScreenUpdating As Boolean
Dim EnableEvents As Boolean
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 LibraryType As String
Dim URLNameSpaceExtended As String
Dim LocalPartialPath As String
Dim PartialPathRootDirectory As String
Dim LocalPartialPathWithFirstFolder As String
Dim LocalPartialPathWithoutFirstFolder As String
Dim Pass As Long
Dim ExistsCount As Long
Dim LogWorksheet As Worksheet
Dim EntryCount As Long
#If Debugging Then
Calculation = Application.Calculation
Application.Calculation = xlCalculationManual
ScreenUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False
EnableEvents = Application.EnableEvents
Application.EnableEvents = False
On Error Resume Next
Set LogWorksheet = ThisWorkbook.Worksheets("Debugging Log")
On Error GoTo 0
If LogWorksheet Is Nothing Then
Set LogWorksheet = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
LogWorksheet.Name = "Debugging Log"
End If
LogWorksheet.Cells.ClearContents
LogWorksheet.Columns(1).Font.Name = "Consolas"
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 0, "Entering OneDriveLocalFilePath"
#End If
' Default to the full name property of ThisWorkbook
If Len(OneDriveFilePath) = 0 Then
OneDriveFilePath = ThisWorkbook.FullName
End If
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 0, "OneDrive file path: " & OneDriveFilePath
' Determine 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")
For Pass = 1 To 2
ExistsCount = 0
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 0, "Evaluating registry entries in '" & RegistryPath & "'"
' Enumerate the key HKEY_CURRENT_USER\SOFTWARE\SyncEngines\Providers\OneDrive
If WinMgmtS.EnumKey(&H80000001, Mid(RegistryPath, InStr(RegistryPath, "\") + 1), RegistryKeys, Types) = 0 Then
EntryCount = 0
For Each RegistryKey In RegistryKeys
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet
' Each key has three values of interest:
'
' 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
On Error Resume Next
CID = WScript.RegRead(RegistryPath & RegistryKey & "\CID")
MountPoint = WScript.RegRead(RegistryPath & RegistryKey & "\MountPoint")
URLNamespace = WScript.RegRead(RegistryPath & RegistryKey & "\URLNamespace")
LibraryType = WScript.RegRead(RegistryPath & RegistryKey & "\LibraryType")
On Error GoTo 0
EntryCount = EntryCount + 1
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 0, "Evaluating registry entry " & EntryCount
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 1, "Key:", (RegistryKey)
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 1, "CID:", CID
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 1, "URLNamespace:", URLNamespace
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 1, "MountPoint:", MountPoint
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 1, "LibraryType:", LibraryType
' Remove any trailing slash from the URL name space
If Right(URLNamespace, 1) = "/" Then
URLNamespace = Left(URLNamespace, Len(URLNamespace) - 1)
End If
' Determine the extended URL name space which may or may not include the CID
If Len(CID) = 0 Then
URLNameSpaceExtended = URLNamespace
Else
URLNameSpaceExtended = URLNamespace & "/" & CID
If Left(OneDriveFilePath, Len(URLNameSpaceExtended)) <> URLNameSpaceExtended Then
URLNameSpaceExtended = URLNamespace
End If
End If
' Looking for a local file path only if the base of the path being evaluated matches the extended URL name space
If Left(OneDriveFilePath, Len(URLNameSpaceExtended)) = URLNameSpaceExtended Then
LocalPartialPath = Mid(OneDriveFilePath, Len(URLNameSpaceExtended) + 2)
' It's not always clear if the first directory in the partial path is used locally, it may be a firectory only in the cloud to distinguish directories shared by others
If InStr(LocalPartialPath, "/") > 0 Then
PartialPathRootDirectory = Left(LocalPartialPath, InStr(LocalPartialPath, "/") - 1)
Else
PartialPathRootDirectory = vbNullString
End If
If Pass = 1 Or Pass = 2 And LibraryType = "teamsite" And Right(MountPoint, Len(PartialPathRootDirectory)) = PartialPathRootDirectory Then
' Build two local partial paths: one with the first folder after the mount point, and the second without the first folder after the mount point
LocalPartialPathWithFirstFolder = Replace(Replace(Mid(OneDriveFilePath, Len(URLNameSpaceExtended) + 2), "/", "\"), "%20", Space(1))
LocalPartialPathWithoutFirstFolder = Mid(LocalPartialPathWithFirstFolder, InStr(LocalPartialPathWithFirstFolder, "\") + 1)
If LocalPartialPathWithFirstFolder = LocalPartialPathWithoutFirstFolder Then
LocalPartialPathWithFirstFolder = vbNullString
End If
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 1, "Local partial path without first folder:", LocalPartialPathWithoutFirstFolder
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 1, "Local partial path with first folder:", LocalPartialPathWithFirstFolder
If OneDriveLocalFilePath_FileExists(MountPoint & "\" & LocalPartialPathWithoutFirstFolder, ConfirmedFilePath, ExistsCount, LogWorksheet) Then Exit For
If Len(LocalPartialPathWithFirstFolder) > 0 Then
If OneDriveLocalFilePath_FileExists(MountPoint & "\" & LocalPartialPathWithFirstFolder, ConfirmedFilePath, ExistsCount, LogWorksheet) Then Exit For
End If
Else
If Pass = 2 Then
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 1, "Not a team site or right part of directory of mount point does not match the first directory of the partial path"
End If
End If
Else
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 1, "URL name space does not match base of OneDrive path"
End If
Next RegistryKey
' Return the confirmed file path if a valid path was found
If Len(ConfirmedFilePath) > 0 Then
If ReturnFolderPathOnly Then
Result = Left(ConfirmedFilePath, InStrRev(ConfirmedFilePath, "\") - 1)
Else
Result = ConfirmedFilePath
End If
Else
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 1, "No local path was found"
End If
Else
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 0, "No registry entries were found"
Result = OneDriveFilePath
End If
If ExistsCount < 2 Then Exit For
If Pass = 1 Then
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 0, "More than one existing file was found in the first pass"
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 0, "Doing another pass and adding an extra check for a team site with matching share drive base folder"
End If
Next Pass
Else
' The path is not a URL so return it as-is
Result = OneDriveFilePath
End If
If ExistsCount = 0 Then
If Not ReturnEmptyIfFileNotFound Then
Result = "A local file path to an existing file was not found."
End If
End If
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 1, "OneDrive result: " & Result
#If Debugging Then
LogWorksheet.Columns(1).EntireColumn.AutoFit
Application.Calculation = Calculation
Application.ScreenUpdating = ScreenUpdating
Application.EnableEvents = EnableEvents
#End If
OneDriveLocalFilePath = Result
End Function
Private Function OneDriveLocalFilePath_FileExists( _
ByRef ProposedFilePath As String, _
ByRef ConfirmedFilePath As String, _
ByRef ExistsCount As Long, _
ByVal LogWorksheet As Worksheet _
) As Boolean
' Tests if file path exists. Internal use only.
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 1, "Checking proposed file path", ProposedFilePath
If ProposedFilePath = ConfirmedFilePath Then
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 1, "File path has already been confirmed"
Exit Function
End If
If ExistingFile(ProposedFilePath) Then
ConfirmedFilePath = ProposedFilePath
ExistsCount = ExistsCount + 1
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 1, "File path exists"
Else
OneDriveLocalFilePath_WriteDebuggingLog LogWorksheet, 1, "File path does not exist"
End If
End Function
Private Sub OneDriveLocalFilePath_WriteDebuggingLog( _
ByVal LogWorksheet As Worksheet, _
Optional ByVal Indent As Long, _
Optional ByVal Message1 As String, _
Optional ByVal Message2 As String _
)
' Logs message to debugging log worksheet. Internal use only.
Const IndentSpace As Long = 2
Const SecondMessagePosition As Long = 44
Dim SpaceCount As Long
#If Not Debugging Then
Exit Sub
#End If
If Len(Message1) = 0 Then
LogWorksheet.Cells(Application.CountA(LogWorksheet.Columns(1)) + 1, 1).Value = Space(1)
Else
If Len(Message2) > 0 Then
SpaceCount = SecondMessagePosition - ((Indent * 2) + Len(Message1) + 1)
If SpaceCount > -1 Then
Message2 = Space(SpaceCount) & Message2
Else
Message2 = Message2
End If
End If
LogWorksheet.Cells(Application.CountA(LogWorksheet.Columns(1)) + 1, 1).Value = Space(Indent * IndentSpace) & Message1 & Message2
End If
End Sub
Public Function ExistingFile( _
ByVal FilePath As String _
) As Boolean
' Return 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
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)