FileSearch and replace

[INI]

Good Morning Guys,

I am having difficulties in figuring out if a file exist. Below is the code I have; for some reason, the code does not pass through the if .Execute() > 0 line, instead it jumps to the else statement.I know that the file exist, please can someone take a look at my code? Thank you very much.

Ini

With Application.FileSearch
        .NewSearch
        .Filename = "bldg.25 24hr 1st shift " & Zt & ".xls"""
        .LookIn = "S:\SUPERVISOR_EOS\bld.25 24hr 1st Shift\"
        .SearchSubFolders = True
        If .Execute() > 0 Then
            For i = 1 To .FoundFiles.Count
                pathinfo = .FoundFiles(i)
                Next i
        
            UserResponse = MsgBox("File already exist, overwrite? ", vbYesNo + vbExclamation, "Save Record")
            If UserResponse = vbNo Then
                 Exit Sub
            End If
            If UserResponse = vbYes Then
            Application.ScreenUpdating = False ' turn off the screen updating
                Set wb = Workbooks.Open("C:\SUPERVISOR_EOS\bld.25 24hr 1st Shift\bldg.25 24hr 1st shift" & Zt & ".xls", True, False)
                Sheets("25 NEW LOG").Select
                Range("A1").Select
                Range("A1").Value = UserForm6.DateBox.Value
                Range("D7").Value = UserForm1.txt320FrontPan.Value
                Range("D8").Value = UserForm1.txtRWDPan320.Value
                Range("D9").Value = UserForm1.txtAWDPan320.Value
                Range("E7").Value = UserForm1.txtLoadHRS1.Value
                Range("E8").Value = UserForm1.txtLoadHRS2.Value
                Range("E9").Value = UserForm1.txtLoadHRS3.Value
                
                wb.Save
                wb.Close
                Application.ScreenUpdating = True ' turn off the screen updating
                Call Clear_All
            End If
        Else
                Application.ScreenUpdating = False ' turn off the screen updating
                Set wb = Workbooks.Open("C:\1415PROD\SUPERVISOR_EOS\bld.25 24hr 1st Shift\bldg.25 24hr 1st shift.xls", True, False)
                Sheets("25 NEW LOG").Select
                Range("A1").Select
                Range("A1").Value = UserForm6.DateBox.Value
                Range("D7").Value = UserForm1.txt320FrontPan.Value
                Range("D8").Value = UserForm1.txtRWDPan320.Value
                Range("D9").Value = UserForm1.txtAWDPan320.Value
                Range("E7").Value = UserForm1.txtLoadHRS1.Value
                Range("E8").Value = UserForm1.txtLoadHRS2.Value
                Range("E9").Value = UserForm1.txtLoadHRS3.Value
                wb.Save
                wb.SaveAs Filename:="C:\SUPERVISOR_EOS\bld.25 24hr 1st Shift\bldg.25 24hr 1st shift" & Zt & ".xls", FileFormat:=xlWorkbookNormal, addtomru:=True
                
                wb.Close
                Application.ScreenUpdating = True ' turn off the screen updating
                Call Clear_All
        End If
        
       End With

[williamrojas78]

Hi

to see if a file exists, try this:

Dim fso As New FileSystemObject
Dim pth
pth = 'add here the location

If Not fso.FileExists(pth & "\filename.xxx") Then
   
      ' the file exists
   ....
   ....
   ....

else
   ....
   ....
   ....
   ....

End If

regards

[INI]

Thank you very much, I'll try it out :).

Ini

[Comatose]

Fooey, use the DIR command.....(stick this in a code module):

public function FileExists(sPath as string) as boolean
If Dir(spath, vbnormal) <> "" Then
     FileExists = True
Else
     FileExists = False
End if
end function

Then call it from your code like this:

if fileexists("c:\fileinquestion.txt") = true then
     msgbox "the file exists"
else
     msgbox "the file dos not exist"
endif