macro to open all files in a folder, save them, then close them

[hopefulhart]

hello,
i have a folder containing many(and many more to come) .xlsx (2007) files that are linked to other files in another folder. all i want is a macro that opens each file (so they can update), save them, and then close them again. at first i tried to record a macro and just go through those actions. i got as far as opening them and each file was listed in the vba code which made it very long. since i will be adding files from time to time i figured that would be way too much work to add each file individually to the code, im thinking there has to be a better way.


thanks for the help
hart

[Trebor76]

Hi hopefulhart,

See how this goes:

Option Explicit

'Written by Trebor76
'Visit  my website www.excelguru.net.au

'Opens all files in the 'strFolderName' directory and then saves them on closing.

'http://www.excelforum.com/excel-programming-vba-macros/964198-macro-to-open-all-files-in-a-folder-save-them-then-close-them.html

Sub Macro1()

   Dim strFolderName As String
   Dim strFileName As String
   Dim wrkMyWrkbook As Workbook
   
   strFolderName = "C:\Temp\" 'Folder containing the files. Change to suit, but don't forget the trailing backslash '\'
   strFileName = Dir(strFolderName & "*.xls*") 'Only open Excel related files.
   
   Application.ScreenUpdating = False
   
   Do Until strFileName = ""
      Set wrkMyWrkbook = Workbooks.Open(strFolderName & strFileName)
      wrkMyWrkbook.Close SaveChanges:=True
      strFileName = Dir()
   Loop
   
   Application.ScreenUpdating = True

End Sub

Regards,

Robert

[hopefulhart]

thanks Roberts, just what i wanted. works great

[Trebor76]

That's great - thanks for letting us know and for marking the thread as solved