PDA

View Full Version : Solved: How to rename text file to date and time format



rajkumar
10-28-2009, 11:56 AM
Hi Experts,

I need help in renaming a text file after it is imported to excel and saved as xls. The imported text file name is like "LOG-CHE.txt"

I have a loop in my macro which imports all file one by one if more than one text file present in the same folder.

Some time i would like to import log-che.txt and log-pun.txt on the first day and log-kol.txt on the next day.

Currently what happens is the looping macro (imports all old files)does import log-che.txt,log-pun.txt again and then imports log-kol.txt when i run the macro on next day.

To tackle this issue what i have done is, renamed the text file by substituting an "_" for a "-".

So once the log-che.txt,log-pun.txt are imported, the file name becomes log_che.txt & log_pun.txt, when i run the macro again by next day, it imports only the log-kol.txt since the "-" (hyphen) is missing in the file name.

But i want to rename the text file as "Log Run @ DD-MMM-YY hh:mm:ss.txt"

here is the end portion of my macro, kindly help

Application.DisplayAlerts = False
sSavename = strpath & "LOG\" & Left(fName, Len(fName) - 4) & ".xls"
ActiveWorkbook.SaveAs FileName:=sSavename, FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True

Dim oldName As String
Dim newName As String
oldName = strpath & "LOG\" & fName
newName = strpath & "LOG\" & WorksheetFunction.Substitute(fName, "-", "_")
Name oldName As newName



Raj : pray2: :help

CreganTur
10-28-2009, 01:56 PM
'shows hour in military time
Format(Now,"mm-dd-yy hh-mm-ss")

'shows 12 base hour with am/pm identifier
Format(Now,"mm-dd-yy h-mm-ss am/pm")

You cannot use colons as a part of a filename- you'll have to use hyphens instead.

mdmackillop
10-28-2009, 03:01 PM
If you need to sort the files in Explorer then consider formatting as
"yy-mm-dd hh-mm-ss"

rajkumar
10-28-2009, 09:06 PM
Thanks both of you,
:friends: It is working as expected. Thanks again

Raj