VBA Macro Error 76; File Path not found

[81jamesacct]

Hi all,
I am new to macros and have been trying to maintain our department's macros that were built by someone who is no longer with the company. The macros are used to pull store data such as address, city, general ledger information and other data. I tried to make some changes to add some additional features which worked for about a month then suddenly crashed. I deleted the broken macros/addins and resaved the backup. Upon doing so, caused everyone to resave the add-in file or update on the macros. Unfortunately, there a few folks that when they try to turn on the macros they receive the error 76, path not found. I assisted everyone on updating the file on their computers and everyone has access to the corresponding network drive the reference tables are saved. Which is throwing me off, its the same paths, same steps, same access just different computers.
Sub UpdateLocalData()

Dim FSO As New Scripting.FileSystemObject

If Not FilePathsNamesSet Then SetFilenamesAndPaths

If Dir(NetworkDataPath & DataFileName) = "" Then Exit Sub
If Dir(LocalDataPath & DataFileName) = "" Then GoTo UpdateLocal:

If FileDateTime(NetworkDataPath & DataFileName) > FileDateTime(LocalDataPath & DataFileName) Then
GoTo UpdateLocal:
End If

Exit Sub

UpdateLocal:

FSO.CopyFile Source:=NetworkDataPath & DataFileName, _
Destination:=LocalDataPath & DataFileName, _
overwritefiles:=True


End Sub



Bolded portion is the part that debugger says path is not found.

 

[rlv01]

It usually means that your file path or file name is bad. Try adding code to help you (and your users) understand which part is invalid. One example:

Sub UpdateLocalData()
    Dim FSO As New Scripting.FileSystemObject
    Dim X As String
    
    If Not FilePathsNamesSet Then SetFilenamesAndPaths
    
    'If Dir(NetworkDataPath & DataFileName) = "" Then Exit Sub
    'If Dir(LocalDataPath & DataFileName) = "" Then GoTo UpdateLocal:
    
    X = NetworkDataPath
    If Not FSO.FolderExists(X) Then
        MsgBox "Folder '" & X & "' not found", vbCritical, "Folder Check"
        Exit Sub
    End If
    
    X = NetworkDataPath & DataFileName
    If Not FSO.FileExists(X) Then
        MsgBox "File '" & X & "' not found", vbCritical, "File Check"
        Exit Sub
    End If
    
    X = LocalDataPath
    If Not FSO.FolderExists(X) Then
        MsgBox "Folder '" & X & "' not found", vbCritical, "Folder Check"
        Exit Sub
    End If
    
    X = LocalDataPath & DataFileName
    If Not FSO.FileExists(X) Then
        MsgBox "File '" & X & "' not found", vbCritical, "File Check"
        Exit Sub
    End If
    
    If FileDateTime(NetworkDataPath & DataFileName) > FileDateTime(LocalDataPath & DataFileName) Then
        GoTo UpdateLocal:
    End If
    
    Exit Sub
    
UpdateLocal:
    
    FSO.CopyFile Source:=NetworkDataPath & DataFileName, Destination:=LocalDataPath & DataFileName, overwritefiles:=True
End Sub

 

[81jamesacct]

It usually means that your file path or file name is bad. Try adding code to help you (and your users) understand which part is invalid. One example:

Sub UpdateLocalData()
    Dim FSO As New Scripting.FileSystemObject
    Dim X As String
   
    If Not FilePathsNamesSet Then SetFilenamesAndPaths
   
    'If Dir(NetworkDataPath & DataFileName) = "" Then Exit Sub
    'If Dir(LocalDataPath & DataFileName) = "" Then GoTo UpdateLocal:
   
    X = NetworkDataPath
    If Not FSO.FolderExists(X) Then
        MsgBox "Folder '" & X & "' not found", vbCritical, "Folder Check"
        Exit Sub
    End If
   
    X = NetworkDataPath & DataFileName
    If Not FSO.FileExists(X) Then
        MsgBox "File '" & X & "' not found", vbCritical, "File Check"
        Exit Sub
    End If
   
    X = LocalDataPath
    If Not FSO.FolderExists(X) Then
        MsgBox "Folder '" & X & "' not found", vbCritical, "Folder Check"
        Exit Sub
    End If
   
    X = LocalDataPath & DataFileName
    If Not FSO.FileExists(X) Then
        MsgBox "File '" & X & "' not found", vbCritical, "File Check"
        Exit Sub
    End If
   
    If FileDateTime(NetworkDataPath & DataFileName) > FileDateTime(LocalDataPath & DataFileName) Then
        GoTo UpdateLocal:
    End If
   
    Exit Sub
   
UpdateLocal:
   
    FSO.CopyFile Source:=NetworkDataPath & DataFileName, Destination:=LocalDataPath & DataFileName, overwritefiles:=True
End Sub

Thanks for the advice and I will try that. Curious why it would be bad for a couple people and not the mass that uses it? Locations, paths, and networks are all the same.

 

[81jamesacct]

Thanks for the advice and I will try that. Curious why it would be bad for a couple people and not the mass that uses it? Locations, paths, and networks are all the same.

It usually means that your file path or file name is bad. Try adding code to help you (and your users) understand which part is invalid. One example:

Sub UpdateLocalData()
    Dim FSO As New Scripting.FileSystemObject
    Dim X As String
   
    If Not FilePathsNamesSet Then SetFilenamesAndPaths
   
    'If Dir(NetworkDataPath & DataFileName) = "" Then Exit Sub
    'If Dir(LocalDataPath & DataFileName) = "" Then GoTo UpdateLocal:
   
    X = NetworkDataPath
    If Not FSO.FolderExists(X) Then
        MsgBox "Folder '" & X & "' not found", vbCritical, "Folder Check"
        Exit Sub
    End If
   
    X = NetworkDataPath & DataFileName
    If Not FSO.FileExists(X) Then
        MsgBox "File '" & X & "' not found", vbCritical, "File Check"
        Exit Sub
    End If
   
    X = LocalDataPath
    If Not FSO.FolderExists(X) Then
        MsgBox "Folder '" & X & "' not found", vbCritical, "Folder Check"
        Exit Sub
    End If
   
    X = LocalDataPath & DataFileName
    If Not FSO.FileExists(X) Then
        MsgBox "File '" & X & "' not found", vbCritical, "File Check"
        Exit Sub
    End If
   
    If FileDateTime(NetworkDataPath & DataFileName) > FileDateTime(LocalDataPath & DataFileName) Then
        GoTo UpdateLocal:
    End If
   
    Exit Sub
   
UpdateLocal:
   
    FSO.CopyFile Source:=NetworkDataPath & DataFileName, Destination:=LocalDataPath & DataFileName, overwritefiles:=True
End Sub

So, I threw that check into VBA section, and it skips over that section and still errors out on the same section. I tried to select and run that specific coding but still errors out in the 'Updatelocal' section

 

[rlv01]

Does Microsoft OneDrive or SharePoint factor into this? That is, are the paths being used standard windows filesystem paths, or are you attempting to use 'https' style OneDrive paths?

 

[81jamesacct]

Does Microsoft OneDrive or SharePoint factor into this? That is, are the paths being used standard windows filesystem paths, or are you attempting to use 'https' style OneDrive paths?

Does not appear to be the case, below are the paths that are set -
Sub SetFilenamesAndPaths()

UpdatePath = "O:\30_days\StoreAttributes\"
NetworkDataPath = "O:\30_days\u9x\"
LocalDataPath = Replace(Application.DefaultFilePath & Application.PathSeparator, "Speedway", "7-Eleven, Inc")
LocalAddInPath = Application.UserLibraryPath
DataFileName = "Store Data Formula Reference Tables.xlsm"
UpdateFileName = "StoreAttributes.xls"
AddInName = "Store Data Add-In"
AddInControl = "Store Data Control"
InstFileExt = ".xlam"
UninstFileExt = ".xlsm"
FilePathsNamesSet = True

All users have access to the o: drive and 30_day folder. I have ensured that these locations are trusted on their computers as well. Names on the files and Addinname and control match to the add-ins saved on their drives as well. I am unsure about where the localdatapath and localaddinpath direct to; I assume that's the users C drive where the add-ins are saved.

I did just check the updatefilename and the file is currently saved as StoreAttributes.xlsx instead of xls as the code states, but if thats the error it should be an error for all.