Save File

S

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
 
J

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
 
S

Steve

Thank you!

Joel said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top