Searching a directory for a specific file

[sellert]

Greetings,
I have a database that after processing an Excel file it moves the xls file to a history folder. I want the system to validate that the xls file in the working directory doesn't already exist in the history directory. I have the following code below that I used and it seemed to work but for some reason now it isn't. See code below...

Public Sub FindExistingHist(ByVal workingFolder As String)
'Searches for the SourceFile$ in the History directory
Dim fs As FileSystemObject
Dim f As folder
Dim file As file
Dim fls
Dim SourceFile$, ext$
Dim response$

MsgBox = "FE Working dir is: " & workingFolder 'Trouble shooting
'Initialize variables
Set fs = New FileSystemObject
Set f = fs.GetFolder(workingFolder)
FilExistFlag = False

For Each file In f.Files
ext$ = UCase(Right(file.Name, 3)) 'get the extension of the found file
If ext$ = "XLS" Then
SourceFile$ = file.Name
Set fls = Application.FileSearch
With fls
.LookIn = HistDir$
.FileName = SourceFile$

If .Execute > 0 Then
MsgBox SourceFile$ & " already exists in history." & vbCrLf & vbCrLf & _
"To Load the files into EPSI you must first change the file name" & vbCrLf & _
"in one of the two directories below:" & vbCrLf & vbCrLf & _
" > Working Directory: " & wrkdir$ & vbCrLf & vbCrLf & _
" > History Directory: " & HistDir$, vbCritical + vbOKOnly, "LOAD ERROR"
FilExistFlag = True
End If
End With
End If
Next
End Sub

I have physically checked and the file that is the Sourcefile is not in the history directory which is what the workingfolder is.

The with fls .filename = SourceFile$, instead of looking in the History directory for a file name stored in SourceFile$ it is storing that file name to .FileName.


Thanks,
SELLERT

If I'm not learning I must be coasting. If I am coasting I must be going down hill.

 

[Darrylles]

Hi sellert,

"It was working but now it isn't" doesn't help one iota.

Any further info. re: actual problem description?


ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[JerryKlmns]

I have rewritten your logic with just Scripting.FileSystemObject

I prefer to declare the variables as objects and not adding the reference to that library.

Public Sub FindExistingHist(ByVal workingFolder As String)
    'Searches for the SourceFile$ in the History directory
    Dim fso As Object 'Scripting.FileSystemObject
    Dim fld As Object 'Scripting.Folder
    Dim fl As Object 'Scripting.File
    Dim response$
    
    MsgBox "FE Working dir is: " & workingFolder  'Trouble shooting
    'Initialize variables
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(workingFolder)
    FilExistFlag = False
    For Each fl In fld.Files
        If fso.GetExtensionName(fl.Name) = "xls" And _
           fso.FileExists(HistDir$ & "\" & fl.Name) Then
               MsgBox fl.Name & " already exists in history." & vbCrLf & vbCrLf & _
                    "To Load the files into EPSI you must first change the file name" & vbCrLf & _
                    "in one of the two directories below:" & vbCrLf & vbCrLf & _
                    "   > Working Directory: " & workingFolder & vbCrLf & vbCrLf & _
                    "   > History Directory: " & HistDir$, vbCritical + vbOKOnly, "LOAD ERROR"
                    FilExistFlag = True
        Else
           fso.MoveFile workingFolder & "\" & fl.Name, HistDir$ & "\" & fl.Name
        End If
    Next
    Set fl = Nothing
    Set fld = Nothing
    Set fso = Nothing

End Sub

 

[sellert]

Thanks for the help Jerry!!

Thanks,
SELLERT

If I'm not learning I must be coasting. If I am coasting I must be going down hill.