Save all open files with current workbookName & text

[maruseru]

On a daily base I have to check around 60 excel files and execute some macros. Open all files and run various macros on them works fine.
By now all the open files should be saved in another folder. In addition the filename should have a text string at the end.

I would like to use a combination of the two macros below.
Save all open files in a defined folder
Each file should have additional text in the name e.g
file name before SaveAS: Test_Data.xlsx
file name after SaveAs: Test_Data - approved.xlsx

1 2 3 4 5

Sub Close_All_WB() Dim wb as workbook For Each wb In Workbooks If wb.Name <> ThisWorkbook.Name Then For Each wb In Application.Workbooks wb.Save Next wb Sub End When save the file individually the file will be saved with the & " - approved", but at the end of the file after the extension .xlxs e.g. Test_Data.xlsx-approved Sub File_on_UNC() Application.DisplayAlerts = False ActiveWorkbook.SaveAs "\\Server\REPORTS\" & ActiveWorkbook.Name & " - approved", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False Application.DisplayAlerts = True End Sub

 

[mumps]

If you save the file with " - approved" after the extension, it will be saved in a format that Excel does not recognize and an error will be generated when you try to open it. However, you will still be able to open it.

 

[maruseru]

thanks for you reply mumps. I still work on the correct file name when it is moved.
file name before SaveAS: Test_Data.xlsx
file name after SaveAs: Test_Data - approved.xlsx

 

[mumps]

So is the macro working properly for you now?

 

[maruseru]

Hi mumps

Have changed it to:
ActiveWorkbook.Saveas Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) - 5) & "approvedI.xls"

 

[maruseru]

Thanks mumps to show me the correct way to solve this issue

 

[mumps]

You are very welcome.