Home » Forums » Open Source Learning Center » Question and Answer

Convert File Names from Date to Day

Submitted by Taimoor on 25 May, 2011 - 11:25

Hi,

I am trying to move files from one location to the another. The file names are in ABC_20110514.xls format. I only want to move files for last week i.e. ABC_20110516.xls to ABC_20110520.xls. Once I move the five files I want to rename them by getting rid of the dates in the name and just use the day name instead e.g. change ABC_20110516.xls to something like Monday.xls. The reason is because I want Monday.xls or Tuesday.xls to be updated every week with the new data. Could anyone please help!

‹ Excel Macro Pivot Table(Access Table) Require Solution Complex IF statement ›
»

I wonder

Submitted by backwards on 25 May, 2011 - 11:43.

Is moving 5 files once per week worth automating?

Although you're moving Excel files, I don't think Excel is suitable for the job.

»

Actually i have to move these

Submitted by Taimoor on 25 May, 2011 - 12:47.

Actually i have to move these files everyweek on a Friday as they are being the picked up by another model. Also it needs to be an automated procdure as part of the controls.

»

move files

Submitted by Anonymous on 25 May, 2011 - 14:51.
Have a look at:

Kill FilePath - deletes the old file

FileCopy Sourcefile, DestinationFile - copies a file from one directory to another

To get the day of the week from the file name, use this: 

Sub GetDayOfWeek()
 
Position1 = InStr(1, "ABC_20110514.xls", "_")
'- find the position of the "_"

Position2 = InStr(1, "ABC_20110514.xls", ".")
'- find the position of the "."

DateString = Mid("ABC_20110514.xls", Position1 + 1, Position2 - Position1 - 1)
 
myYear = Left(DateString, 4)
myMonth = Mid(DateString, 5, 2)
myDay = Right(DateString, 2)
 
DayOfWeek = Format(myDay & "-" & myMonth & "-" & myYear, "ddd")
MsgBox DayOfWeek
 
End Sub

then:

name OldFileName as NewFileName - use this to rename your date stamped file as the day of the week.

»

Thank you very much for

Submitted by Taimoor on 26 May, 2011 - 08:33.

Thank you very much for taking the time our out and helping me. I am actually very new to VBA so could you please explain the comments '- find the position of the "_"
and '- find the position of the "."

»

Your file name is

Submitted by backwards on 26 May, 2011 - 13:45.

Your file name is ABC_20110514.xls

So in order to get the day, you need to get the date that is in the file name. It is between the _ and the .

What his code does is to get the position of those characters, then pick up the string in between it. The net effect is it will get 20110514

»

Thank you!

Submitted by Taimoor on 27 May, 2011 - 10:10.

Thank you!

»