Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming files consecutively with VBA
I have a macro to move the files from one folder to an archive folder and
rename them with the modified date. However I have some users who are trying to run the macro several times a day, I had added an exception that if the file already existed to add a _01 on the end. Is there a way to make it so the macro will count up from _01, _02, _03, etc when the file already exists until it finds an unused number? The code does a lot of other things as well, but this is the section I am wanting to modify. On Error GoTo ErrHandler For Each objFile In objFolder.Files 'for every file in the folder... 'parse the name in three pieces, file name middle and extension. In between, insert the 'last modified date. Other options may be a native Date function or a cell refernce to 'tag the renamed file in place of =====Format(objFile.DateLastModified, "_mmm_dd_yy")===<<<< 'if strMid is not used, it can be removed or left as a null "" string strName = Left(objFile.Name, Len(objFile.Name) - 4) 'remove extension and leave name only 'strName = Range("A1") 'sample of renaming from cell A1, can by used for strMid as well strMid = Format(objFile.DateLastModified, "mm-dd-yy") 'insert and format files date modified into name 'strMid = Format(Now(),"_mmm_dd_yy") 'sample of formatting the current date into the file name strExt = Right(objFile.Name, 4) 'the original file extension strNewFileName = strName & strMid & strExt strAltFileName = strName & strMid & "_01" & strExt objFile.Move strDestFolder & "\" & strNewFileName Continue ErrHandler: Debug.Print Err.Number, Err.Description If Err.Number = 58 Then Err.Clear objFile.Move strDestFolder & "\" & strAltFileName GoTo Continue |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming files consecutively with VBA
Why not just add in a time stamp:
strMid = Format(objFile.DateLastModified, "mm-dd-yy") & Format(Now, "-hh-mm-ss") But this would be preferable, to allow sorting by name and getting the files sorted by date as well: strMid = Format(objFile.DateLastModified, "yyyy-mm-dd") & Format(Now, "-hh-mm-ss") HTH, Bernie MS Excel MVP "Denise Pollock" wrote in message ... I have a macro to move the files from one folder to an archive folder and rename them with the modified date. However I have some users who are trying to run the macro several times a day, I had added an exception that if the file already existed to add a _01 on the end. Is there a way to make it so the macro will count up from _01, _02, _03, etc when the file already exists until it finds an unused number? The code does a lot of other things as well, but this is the section I am wanting to modify. On Error GoTo ErrHandler For Each objFile In objFolder.Files 'for every file in the folder... 'parse the name in three pieces, file name middle and extension. In between, insert the 'last modified date. Other options may be a native Date function or a cell refernce to 'tag the renamed file in place of =====Format(objFile.DateLastModified, "_mmm_dd_yy")===<<<< 'if strMid is not used, it can be removed or left as a null "" string strName = Left(objFile.Name, Len(objFile.Name) - 4) 'remove extension and leave name only 'strName = Range("A1") 'sample of renaming from cell A1, can by used for strMid as well strMid = Format(objFile.DateLastModified, "mm-dd-yy") 'insert and format files date modified into name 'strMid = Format(Now(),"_mmm_dd_yy") 'sample of formatting the current date into the file name strExt = Right(objFile.Name, 4) 'the original file extension strNewFileName = strName & strMid & strExt strAltFileName = strName & strMid & "_01" & strExt objFile.Move strDestFolder & "\" & strNewFileName Continue ErrHandler: Debug.Print Err.Number, Err.Description If Err.Number = 58 Then Err.Clear objFile.Move strDestFolder & "\" & strAltFileName GoTo Continue |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming files consecutively with VBA
Thanks for the idea that would work. However my boss has now decided he does
not want to keep extra files for the same date and would rather they overwrite. But I can't seem to figure out how to make it overwrite the file. Since it is a move instead of a save turning the alerts off does not work. Do you know what I have to change to make it overwrite the file if it already exists? "Bernie Deitrick" wrote: Why not just add in a time stamp: strMid = Format(objFile.DateLastModified, "mm-dd-yy") & Format(Now, "-hh-mm-ss") But this would be preferable, to allow sorting by name and getting the files sorted by date as well: strMid = Format(objFile.DateLastModified, "yyyy-mm-dd") & Format(Now, "-hh-mm-ss") HTH, Bernie MS Excel MVP "Denise Pollock" wrote in message ... I have a macro to move the files from one folder to an archive folder and rename them with the modified date. However I have some users who are trying to run the macro several times a day, I had added an exception that if the file already existed to add a _01 on the end. Is there a way to make it so the macro will count up from _01, _02, _03, etc when the file already exists until it finds an unused number? The code does a lot of other things as well, but this is the section I am wanting to modify. On Error GoTo ErrHandler For Each objFile In objFolder.Files 'for every file in the folder... 'parse the name in three pieces, file name middle and extension. In between, insert the 'last modified date. Other options may be a native Date function or a cell refernce to 'tag the renamed file in place of =====Format(objFile.DateLastModified, "_mmm_dd_yy")===<<<< 'if strMid is not used, it can be removed or left as a null "" string strName = Left(objFile.Name, Len(objFile.Name) - 4) 'remove extension and leave name only 'strName = Range("A1") 'sample of renaming from cell A1, can by used for strMid as well strMid = Format(objFile.DateLastModified, "mm-dd-yy") 'insert and format files date modified into name 'strMid = Format(Now(),"_mmm_dd_yy") 'sample of formatting the current date into the file name strExt = Right(objFile.Name, 4) 'the original file extension strNewFileName = strName & strMid & strExt strAltFileName = strName & strMid & "_01" & strExt objFile.Move strDestFolder & "\" & strNewFileName Continue ErrHandler: Debug.Print Err.Number, Err.Description If Err.Number = 58 Then Err.Clear objFile.Move strDestFolder & "\" & strAltFileName GoTo Continue |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming files consecutively with VBA
Given your logic, I _think_ you could use the error handler to remove the original:
If Err.Number = 58 Then Err.Clear Kill strDestFolder & "\" & strNewFileName objFile.Move strDestFolder & "\" & strNewFileName HTH, Bernie MS Excel MVP "Denise Pollock" wrote in message ... Thanks for the idea that would work. However my boss has now decided he does not want to keep extra files for the same date and would rather they overwrite. But I can't seem to figure out how to make it overwrite the file. Since it is a move instead of a save turning the alerts off does not work. Do you know what I have to change to make it overwrite the file if it already exists? "Bernie Deitrick" wrote: Why not just add in a time stamp: strMid = Format(objFile.DateLastModified, "mm-dd-yy") & Format(Now, "-hh-mm-ss") But this would be preferable, to allow sorting by name and getting the files sorted by date as well: strMid = Format(objFile.DateLastModified, "yyyy-mm-dd") & Format(Now, "-hh-mm-ss") HTH, Bernie MS Excel MVP "Denise Pollock" wrote in message ... I have a macro to move the files from one folder to an archive folder and rename them with the modified date. However I have some users who are trying to run the macro several times a day, I had added an exception that if the file already existed to add a _01 on the end. Is there a way to make it so the macro will count up from _01, _02, _03, etc when the file already exists until it finds an unused number? The code does a lot of other things as well, but this is the section I am wanting to modify. On Error GoTo ErrHandler For Each objFile In objFolder.Files 'for every file in the folder... 'parse the name in three pieces, file name middle and extension. In between, insert the 'last modified date. Other options may be a native Date function or a cell refernce to 'tag the renamed file in place of =====Format(objFile.DateLastModified, "_mmm_dd_yy")===<<<< 'if strMid is not used, it can be removed or left as a null "" string strName = Left(objFile.Name, Len(objFile.Name) - 4) 'remove extension and leave name only 'strName = Range("A1") 'sample of renaming from cell A1, can by used for strMid as well strMid = Format(objFile.DateLastModified, "mm-dd-yy") 'insert and format files date modified into name 'strMid = Format(Now(),"_mmm_dd_yy") 'sample of formatting the current date into the file name strExt = Right(objFile.Name, 4) 'the original file extension strNewFileName = strName & strMid & strExt strAltFileName = strName & strMid & "_01" & strExt objFile.Move strDestFolder & "\" & strNewFileName Continue ErrHandler: Debug.Print Err.Number, Err.Description If Err.Number = 58 Then Err.Clear objFile.Move strDestFolder & "\" & strAltFileName GoTo Continue |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming files consecutively with VBA
That did the trick, thank you very much for your help.
Happy Holidays! "Bernie Deitrick" wrote: Given your logic, I _think_ you could use the error handler to remove the original: If Err.Number = 58 Then Err.Clear Kill strDestFolder & "\" & strNewFileName objFile.Move strDestFolder & "\" & strNewFileName HTH, Bernie MS Excel MVP "Denise Pollock" wrote in message ... Thanks for the idea that would work. However my boss has now decided he does not want to keep extra files for the same date and would rather they overwrite. But I can't seem to figure out how to make it overwrite the file. Since it is a move instead of a save turning the alerts off does not work. Do you know what I have to change to make it overwrite the file if it already exists? "Bernie Deitrick" wrote: Why not just add in a time stamp: strMid = Format(objFile.DateLastModified, "mm-dd-yy") & Format(Now, "-hh-mm-ss") But this would be preferable, to allow sorting by name and getting the files sorted by date as well: strMid = Format(objFile.DateLastModified, "yyyy-mm-dd") & Format(Now, "-hh-mm-ss") HTH, Bernie MS Excel MVP "Denise Pollock" wrote in message ... I have a macro to move the files from one folder to an archive folder and rename them with the modified date. However I have some users who are trying to run the macro several times a day, I had added an exception that if the file already existed to add a _01 on the end. Is there a way to make it so the macro will count up from _01, _02, _03, etc when the file already exists until it finds an unused number? The code does a lot of other things as well, but this is the section I am wanting to modify. On Error GoTo ErrHandler For Each objFile In objFolder.Files 'for every file in the folder... 'parse the name in three pieces, file name middle and extension. In between, insert the 'last modified date. Other options may be a native Date function or a cell refernce to 'tag the renamed file in place of =====Format(objFile.DateLastModified, "_mmm_dd_yy")===<<<< 'if strMid is not used, it can be removed or left as a null "" string strName = Left(objFile.Name, Len(objFile.Name) - 4) 'remove extension and leave name only 'strName = Range("A1") 'sample of renaming from cell A1, can by used for strMid as well strMid = Format(objFile.DateLastModified, "mm-dd-yy") 'insert and format files date modified into name 'strMid = Format(Now(),"_mmm_dd_yy") 'sample of formatting the current date into the file name strExt = Right(objFile.Name, 4) 'the original file extension strNewFileName = strName & strMid & strExt strAltFileName = strName & strMid & "_01" & strExt objFile.Move strDestFolder & "\" & strNewFileName Continue ErrHandler: Debug.Print Err.Number, Err.Description If Err.Number = 58 Then Err.Clear objFile.Move strDestFolder & "\" & strAltFileName GoTo Continue |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Renaming files | Excel Programming | |||
Renaming Files | Excel Discussion (Misc queries) | |||
Renaming Files: Take 2 | Excel Programming | |||
Renaming files | Excel Programming | |||
Renaming Files | Excel Programming |