GetLocalPath - Runtime Error 76 "Path not found" - already fixed

[4lm1ghty]

Hello,

first of all an incredible big thank you to both of you. This Project is awesome and still supported after so much time, great job!

I already fixed the problem, but i am still curious what caused the issue, so it doesnt happen again. Anyway I can fully understand if there is no time to look into something that isnt a problem anymore.

I recently (like 6 to 8 weeks ago) updated the LibFileTools from here into my personal xlsb and always pull the code from there into each xlsm file i am using it with. I have never had any problems, using this since 2019, maybe earlier. 13 hrs ago I had a perfectly working file with a working GetLocalPath(ThisWorkbook.Path), while doing some tests that were completely unrelated to it, suddenly runtime error 76 "Path not found" on this part Get_Latest_File(FilePath, FileName).

FileName = "*Test*.xlsx"
FilePath = GetLocalPath(ThisWorkbook.Path) 
LatestFile = Get_Latest_File(FilePath, FileName)

Public Function Get_Latest_File(searchPath As String, FileName As String) As String
Dim FSO As Object
Dim FSfolder As Object
Dim FSfile As Object
Get_Latest_File = ""
latestDate = 0
Set FSO = CreateObject("Scripting.FileSystemObject")
Set FSfolder = FSO.GetFolder(searchPath)
For Each FSfile In FSfolder.Files
    If FSfile.Name Like FileName And FSfile.DateLastModified > latestDate Then
        Get_Latest_File = FSfile.Path
        latestDate = FSfile.DateLastModified
        FSFileName = FSfile.Name
    End If
Next
End Function

The Path of the workbook in windows explorer is: "C:\Users\User\OneDrive Company Name & Name\Köln\Stadion Köln"

GetLocalPath(ThisWorkbook.Path) delivered: "C:\Users\User\OneDrive Company Name & Name\Stadion Köln"

The directory after OneDrive was missing. I disabled OneDrive and autosave and it suddenly worked again and included "\Köln\" again in the path.

The first fix i tried was the code by Guido, which worked instantly:

Public Function GetLocalPath(ByVal Path As String) As String
    Const HKCU = &H80000001
    Dim objReg As Object, rPath As String, subKeys(), subKey
    Dim urlNamespace As String, mountPoint As String, secPart As String
    Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\." & _
                           "\root\default:StdRegProv")
    rPath = "Software\SyncEngines\Providers\OneDrive\"
    objReg.EnumKey HKCU, rPath, subKeys
    For Each subKey In subKeys
        objReg.GetStringValue HKCU, rPath & subKey, "UrlNamespace", urlNamespace
        If InStr(Path, urlNamespace) > 0 Then
            objReg.GetStringValue HKCU, rPath & subKey, "MountPoint", mountPoint
            secPart = Replace(Mid(Path, Len(urlNamespace)), "/", "\")
            Path = mountPoint & secPart
            Do Until Dir(Path, vbDirectory) <> "" Or InStr(2, secPart, "\") = 0
                secPart = Mid(secPart, InStr(2, secPart, "\"))
                Path = mountPoint & secPart
            Loop
            Exit For
        End If
    Next
    GetLocalPath = Path
End Function

How likely is it that Windows/OneDrive updated at 2am while the file was open and the code was running seconds earlier? I know its like looking for a needle in a haystack, but why would it not work from literally one second to another.

Again, thank you very much!

Best Regards

[cristianbuse]

Hi @4lm1ghty ,

Thank you for the feedback!

Indeed it would be hard to figure this out, but my best guess is that there was an OneDrive update which caused the issue, in between the calls.

Please note the rebuildCache parameter in both GetLocalPath and GetRemotePath. By default it is set to False as we want to read the OD info as few times as possible. For example, parsing the SQLite database file which is a recent change (dat file was removed entirely a few months ago), can take up to half of a second and so the code only reads the info once and then re-uses it.

If you use GetLocalPath on 1000 files for example, in a loop, then it doesn't make sense to read all the OD info for each file because it would be slow and it's unlikely there is an update to the software or a major change (like a new mapped SharePoint folder) within the time it takes to loop those files. However, for independant bits in your program, it makes sense to set rebuildCache to True which allows you to read all the information again if you want to be 100% sure the account is still connected and the info is up to date.

Happy to answer any questions you might have. Thanks!