Save all open files with current workbookName & text

maruseru

New Member
Joined
Aug 21, 2020
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
(y) 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
 
Upvote 0
So is the macro working properly for you now?
 
Upvote 0
Hi mumps

Have changed it to:
ActiveWorkbook.Saveas Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) - 5) & "approvedI.xls"
 
Upvote 0
Thanks mumps to show me the correct way to solve this issue
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top