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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,097
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

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

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,097
So is the macro working properly for you now?
 

maruseru

New Member
Joined
Aug 21, 2020
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi mumps

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

maruseru

New Member
Joined
Aug 21, 2020
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Thanks mumps to show me the correct way to solve this issue
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

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
Top