Save File

[Steve]

Hello
I would like to look through a folder of files (mypath) that are all named
the same (myfile_20090123_1.xls) find the most recent file and extract the
yyyymmdd_X and use it to save my file as myotherfile_xxxxmmdd_X.xlsx.
I am using this elsewhere to find the most recent file...

Const FilePath = "Mypath"
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set Folder = _
fso.GetFolder(FilePath)

MyDate = 1
Myfile = ""
For Each fl In Folder.Files
PeriodPos = InStrRev(fl.Name, ".")
EXT = Left(fl.Name, 7)
If UCase(EXT) = "My_File" Then
If fl.DateLastModified > MyDate Then
Myfile = fl.Path
MyDate = fl.DateLastModified
End If
End If
Next fl


What do I add to finish this off?

Thanks

 

[Joel]

Since you are using the 1st part of nmy code here is the rest

Const FilePath = "Mypath"
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set Folder = _
fso.GetFolder(FilePath)

MyDate = 1
Myfile = ""
For Each fl In Folder.Files
PeriodPos = InStrRev(fl.Name, ".")
EXT = Left(fl.Name, 7)
If UCase(EXT) = "My_File" Then
If fl.DateLastModified > MyDate Then
Myfile = fl.Path
MyDate = fl.DateLastModified
End If
End If
Next fl

LastSlash = InStrRev(Myfile, "\")
Folder = Left(Myfile, LastSlash)
BaseName = Mid(Myfile, LastSlash + 1)
'find 1st underscore and extract character to left in cluding underscore
FirstName = Left(BaseName, InStr(BaseName, "_"))
'find last underscore and extract character to the right
LastName = Mid(BaseName, InStrRev(BaseName, "_"))

'Create a string for todays date
FileDate = Format(Date, "yyyymmdd")
'Combine all the peice to get new file name
NewFileName = Folder & FirstName & FileDate & LastName

'Copy the old file to the new name
fso.copyfile Myfile, NewFileName

 

[Steve]

Thank you!

Since you are using the 1st part of nmy code here is the rest

Const FilePath = "Mypath"
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set Folder = _
fso.GetFolder(FilePath)

MyDate = 1
Myfile = ""
For Each fl In Folder.Files
PeriodPos = InStrRev(fl.Name, ".")
EXT = Left(fl.Name, 7)
If UCase(EXT) = "My_File" Then
If fl.DateLastModified > MyDate Then
Myfile = fl.Path
MyDate = fl.DateLastModified
End If
End If
Next fl

LastSlash = InStrRev(Myfile, "\")
Folder = Left(Myfile, LastSlash)
BaseName = Mid(Myfile, LastSlash + 1)
'find 1st underscore and extract character to left in cluding underscore
FirstName = Left(BaseName, InStr(BaseName, "_"))
'find last underscore and extract character to the right
LastName = Mid(BaseName, InStrRev(BaseName, "_"))

'Create a string for todays date
FileDate = Format(Date, "yyyymmdd")
'Combine all the peice to get new file name
NewFileName = Folder & FirstName & FileDate & LastName

'Copy the old file to the new name
fso.copyfile Myfile, NewFileName