Search for a Filename

[HamiTipene]

Hi there,

I am trying to create a macro that will search for a filename in a given folder, open it and add a sheet from another workbook automatically. While the rest of the task is relatively simple, I am having trouble writing up code that will do the search successfully. The problem is that the filename is set according to the date. I have to find the latest date, and therefore I cycle from the present date backwards. Below I have pasted some code that theoretically does the task that I found from the web but it is far too SLOW, and appears to crash the system at times. Could anyone suggest an alternative method?

The relevant code is below:

Sub PBPAT_Automate()

Const datapath = "\\Sadnl\dfsnl\GRNL01\1686\CharlesRiverOutput"
test = 100000000

Do Until FileExists = True Or i >= 30
i = i + 1
test = (DateSerial(Year(Now), Month(Now), Day(Now)) - i)
test = Format(test, "yyyy/mm/dd")
test = Replace(test, "/", "")

Call FileSearch(datapath, test)
Loop



Sub FileSearch(datatype, test)

Dim fs As FileSearch
Set fs = Application.FileSearch
Dim i As Integer

fs.NewSearch

With fs
With .PropertyTests
.Add _
Name:="Text or Property", _
Condition:=msoConditionIncludesPhrase, _
Value:="\INGIM.EXP.Position." & test
End With
.LookIn = datapath
.SearchSubFolders = False
.Filename = "*"
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
End With

If fs.Execute() > 0 Then
MsgBox "There were " & _
fs.FoundFiles.Count & _
" file(s) found."
For i = 1 To fs.FoundFiles.Count
MsgBox fs.FoundFiles(i)
Next i
FileExists = True
Else
MsgBox "There were no files found."
FileExists = False
End If

End Sub

 

[xld]

Sub PBPAT_Automate()

Const datapath = "C:\test\" '"\\Sadnl\dfsnl\GRNL01\1686\CharlesRiverOutput\"
Dim FileName As String

    FileName = Dir(datapath & Format(Date - 1, "yyyymmdd") & ".*")
    If FileName = "" Then
    
        MsgBox "Not found file " & datapath & Format(Date - 1, "yyyymmdd")
    Else
    
        MsgBox "Found file " & datapath & Format(Date - 1, "yyyymmdd")
    End If
End Sub

 

[HamiTipene]

Hi,

Thanks for this, this is good code. I was hoping you could help me make one more extension to the code however. The thing is that sometimes there are extra characters attached to the end of the file, so I am wondering if it is possible to search for a string that signifies the filename plus random characters behind it?

 

[xld]

Just add another *

FileName = Dir(datapath & Format(Date - 1, "yyyymmdd") & "*.*")