RunTime Error 53 : File Not Found error

[had1015]

Hello,

I was assisted a few years age by someone in this forum with code for comparing 2 files. Now when I run the code in Windows 7, I get error 53 File not found.




If FileDateTime(mpFile1) < FileDateTime(mpFile2) Then



This is the full code:




Sub UpdateList()
Dim mpFolder As String
Dim mpFile1 As String
Dim mpFile2 As String
Dim mpWB1 As Workbook
Dim mpWB2 As Workbook
Dim mpWB3 As Workbook
Dim mpLastRow As Long
Dim i As Long
Dim relativePath As String

With Application.FileDialog(msoFileDialogFolderPicker)

.AllowMultiSelect = False
If .Show = -1 Then

mpFolder = .SelectedItems(1)
mpFile1 = Dir(mpFolder & Application.PathSeparator &

"*.xlsx", vbNormal)

If mpFile1 <> "" Then

mpFile2 = Dir
End If

If mpFile1 = "" Or mpFile2 = "" Then

MsgBox "Incomplete files - exiting", vbCritical, "File

Details"
Exit Sub
End If

If FileDateTime(mpFile1) < FileDateTime(mpFile2) Then

Set mpWB1 = Workbooks.Open(mpFile1)
Set mpWB2 = Workbooks.Open(mpFile2)
Else
Set mpWB1 = Workbooks.Open(mpFile2)
Set mpWB2 = Workbooks.Open(mpFile1)
End If

mpWB1.Worksheets(1).Copy
Set mpWB3 = ActiveWorkbook

ActiveWorkbook.SaveAs "Updated " & mpWB2.Name,

FileFormat:=51

mpWB2.Worksheets(1).Copy After:=mpWB3.Worksheets(1)

With mpWB3.Worksheets(2)

mpLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
For i = mpLastRow To 1

If .Cells(i, "F").Value =

mpWB3.Worksheets(1).Cells(i, "F").Value Then

.Rows(i).Offset(0, 11).Value =

mpWB1.Worksheets(1).Rows(i).Offset(0, 11).Value & " " &

mpWB3.Worksheets(2).Rows(i).Offset(0, 11).Value
End If
Next i

Application.DisplayAlerts = False
On Error Resume Next

Sheets("Sheet1 (2)").Delete
End With

End If
End With
CloseAll

On Error GoTo 0
Application.DisplayAlerts = True
End Sub
Private Sub CloseAll()
' This sub will close all workbooks
' except the workbook in which the code is located.
Dim WkbkName As Object

On Error GoTo Close_Error
Application.ScreenUpdating = False

For Each WkbkName In Application.Workbooks()
If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close
Next

' If everything runs all right, exit the sub.
Exit Sub

' Error handler.
Close_Error:
MsgBox Str(Err) & " " & Error()
Resume Next
End Sub

Private Sub ExplorePath()
Shell Environ("windir") & "\Explorer.exe " & ActiveWorkbook.Path,

vbMaximizedFocus
End Sub



Thank you for your assistance.

[Bob Phillips]

I can't say I am absolutely sure, but your code looks prone to a problem regardless of Excel version, the file is not properly qualified. Maybe it should be


If FileDateTime(mpFolder & Application.PathSeparator & mpFile1) < FileDateTime(mpFolder & Application.PathSeparator & mpFile2) Then

[had1015]

Thank you xld for your assistance and your reply. I will test your code when I get back to the office; in a couple of hours.

[had1015]

Thanks again xld for your time,

I modified you code and got another runtime error. It listed the file name with runtime error 1004. It error stated to check the spelling and directory. I did but still had the same error. Also if I were trying to open the file from recently used files, ensure it has not been renamed or moved. The error came at this piece of the code:



Set mpWB1 = Workbooks.Open(mpFile1)

[Bob Phillips]

Does mpFile1/2 have the path as part of the name?

[had1015]

Yes, yhey are in the same directory and folder and they each have unique file names. One is version 97-2003 excel file the other is excel 2010 file.

[had1015]

Sorry, to answer your question the path is not part of the file name.

[Bob Phillips]

Then it should be included in that as well.

[had1015]

Thanks again xld for your assistance. Sorry but I don't quite understand. I have two files located in a folder. One file was created one day after the other. Also in the folder exists the workbook containing the macro. The latest file would be updated with comments of the previous file. It worked well for 3 years until now. They are files having such names as "DC00 DAH.xls" or ".xlsx". I'm not sure what you mean.

[Bob Phillips]

This is what I mean




If .Show = -1 Then

mpFolder = .SelectedItems(1)
mpFile1 = Dir(mpFolder & Application.PathSeparator & "*.xlsx", vbNormal)

If mpFile1 <> "" Then

mpFile1 = mpFolder & Application.PathSeparator & mpFile1

mpFile2 = Dir
If mpFile2 <> "" Then

mpFile2 = mpFolder & Application.PathSeparator & mpFile2
End If
End If

If mpFile1 = "" Or mpFile2 = "" Then

MsgBox "Incomplete files - exiting", vbCritical, "File "

Details ""
Exit Sub
End If

If FileDateTime(mpFile1) < FileDateTime(mpFile2) Then

Set mpWB1 = Workbooks.Open(mpFile1)
Set mpWB2 = Workbooks.Open(mpFile2)
Else
Set mpWB1 = Workbooks.Open(mpFile2)
Set mpWB2 = Workbooks.Open(mpFile1)
End If

[had1015]

Thanks xld it works. You're a lifesaver.