Need help with opening files with VBA

[cob98tp]

Morning All,

I need a litle help with a macro I use to open a series of files...

We have a system with produces files each day called...

"example, dd-mm-yyyy hh-mm-ss.xls"

Where dd-mm-yyyy is the date the report was created, and hh-mm-ss is the exact time the report was finished.

The problem is that the time is never the same and not predictable. I want a piece of code that will open the file with the latest date and time in the directory when the macro is run and rename it to just "example.xls", before performing some other code which I already have and definitely works.

The date is usually going to be the previous day - except after a weekend/bank holiday. And it doesn't matter is the file is deleted after use .. so it could be possible to open the only file of the format "example, dd-mm-yyyy hh-mm-ss.xls" I just don't know how...

I'm sure I'm close, I'm just missing a little bit of code to make it all come together.

Thanks in advance!

 

[PeregrinTook]

Cob,

When you run OpenLatestFile it will do exactly that - you can then do a ActiveWorkbook.SaveAs to rename it.

Change path & filename accordingly...

Function LatestFile(ByVal pPathExpression As String) As String
Dim strFile As String
    
strFile = Dir("C:\My Path\example *.xls") 'path & file name - customize as required
LatestFile = strFile
    
Do While strFile <> ""
    If strFile > LatestFile Then LatestFile = strFile
    strFile = Dir
Loop

End Function

Sub OpenLatestFile()
Dim strPath As String
Dim strFileExpr As String
Dim strLatestFile As String

strPath = "C:\My Path\" 'path to search - customize as required
strFileExpr = "example *.xls" 'file names to find - again change to suit

strLatestFile = strPath & LatestFile(strPath & strFileExpr)

Workbooks.Open (strLatestFile)

End Sub

Hope this helps
PT