VBA to Open/Save/Close Multiple Excel Files

MrMaker

Board Regular
Joined
Jun 7, 2018
Messages
56
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I need some code that will loop through a folder of 300 excel files.........I need the code to open each file (which enables some updates), then save it, and then close it.

The format of the files is xlsm.

Probably a simple one for some of you folk

Can anyone help?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Try this

VBA Code:
Sub opensaveclose()
  Dim sPath As String, sFile As Variant
  sPath = "C:\your path\books\"
  sFile = Dir(sPath & "*.xlsm")
  Do While sFile <> ""
    Workbooks.Open (sPath & sFile)
    ActiveWorkbook.Close True
    sFile = Dir()
  Loop
End Sub
 
Upvote 0
Hi again,

So the above doesn't seem to work, can anyone help?

Simple VBA to loop through a folder of 300 workbooks, opening, (allowing the code to run), saving and closing

Much appreciated
 
Upvote 0
I need the code to open each file (which enables some updates), then save it, and then close it.
(allowing the code to run)
In the first post you didn't mention a code. If you have a code to execute you can put it between the open and close statements.
In the code you must also change the folder where you have the books.
 
Upvote 0
Yeah, sorry, I'm not over clued up with this stuff.

So, the code is embedded within each of the 300 reports. The code auto runs when the reports are opened, so I'm not sure if that still needs to be written into this new code, as essentially I'm just asking for the sheet to open, the code to auto run, then a save and close....

Any thoughts?

Really appreciate your time
 
Upvote 0
I'm just asking for the sheet to open, the code to auto run, then a save and close
That's what the macro does, opens the book. -at that moment the code should be executed, if the code is not executed, it is something that you should review in your books-, finally close the book and save it.

You can share the code you have in any of your books, to see in which event you have it.
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

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