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
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