Renaming duplicate file then move into "Done" folder

[Dahlia]

Hi Excel Experts,

I found this awesome vba code to allow me to copy every wsheet from multiple workbooks into one Master Wbook, then move the file to folder named "Done" in the same Master Wbook path. However, when it finds that the file already exists in folder "Done", the macro will stop and point to the Master Workbook.

I need it to be able to automatically rename the file (increment by 1 each time find existing) in parent path (maintaining the same file extension as original) then move to the "Done" folder.

My edited code successfully move the file if found the file not exist in "Done" folder, but still not working if the file has existed. Please help to correct my code:-

Do While FileName <> ""

Workbooks.Open FileName:=myPath & FileName, local:=True, ReadOnly:=True

For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy after:=ThisWorkbook.Sheets("Macros")
Next Sheet

Workbooks(FileName).Close savechanges:=False
If FSO.FileExists(ToDir & FileName) = False Then
MsgBox "file doesn't exist"
FSO.MoveFile Source:=myPath & FileName, Destination:=ToDir
Else
MsgBox "File exist"
NewName = FileName & "(" & j & ")" '<== versioning to differentiate the file
FSO.MoveFile FileName, NewName '<== replace the old file
FSO.MoveFile Source:=myPath & NewName, Destination:=ToDir '<== move to the destination folder
End If

FileName = Dir()
Loop

Thanks in advance.
DZ

 

[xlstime]

in exception (error handing ) part add

rename file current_file_name to current_file_name&variable_name

and on top you can assign variable or store value somewhere in your wbook.

Hi Excel Experts,

I found this awesome vba code to allow me to copy every wsheet from multiple workbooks into one Master Wbook, then move the file to folder named "Done" in the same Master Wbook path. However, when it finds that the file already exists in folder "Done", the macro will stop and point to the Master Workbook.

I need it to be able to automatically rename the file (increment by 1 each time find existing) in parent path (maintaining the same file extension as original) then move to the "Done" folder.

My edited code successfully move the file if found the file not exist in "Done" folder, but still not working if the file has existed. Please help to correct my code:-

Do While FileName <> ""

Workbooks.Open FileName:=myPath & FileName, local:=True, ReadOnly:=True

For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy after:=ThisWorkbook.Sheets("Macros")
Next Sheet

Workbooks(FileName).Close savechanges:=False
If FSO.FileExists(ToDir & FileName) = False Then
MsgBox "file doesn't exist"
FSO.MoveFile Source:=myPath & FileName, Destination:=ToDir
Else
MsgBox "File exist"
NewName = FileName & "(" & j & ")" '<== versioning to differentiate the file
FSO.MoveFile FileName, NewName '<== replace the old file
FSO.MoveFile Source:=myPath & NewName, Destination:=ToDir '<== move to the destination folder
End If

FileName = Dir()
Loop

Thanks in advance.
DZ

 

[Luke M]

Be careful about just concatening to end of filename. I'm guessing that filename includes your extension, so you can't just put the number at end. Depending on how you originally got the value for FileName, it may be easy/hard to get just the portion w/o file extension.

 

[Dahlia]

Hye xlstime,

Thank you for the suggestion. But I would like to make it work in the script rather than need to maintain the value on a worksheet.

Thanks.
DZ