Excel

Process All Workbooks in a Given Directory Folder

Ease of Use

Intermediate

Version tested with

2000 

Submitted by:

Steiner

Description:

Run your code in a folder full of workbooks. 

Discussion:

Sometimes you need code that opens all workbooks in a given directory, does something, and then closes them again. This is just an example to get you started, because it does nothing except print the name of the workbook. 

Code:

instructions for use 

			
Sub ProcessAll(sPath As String) 
    Dim Wb As Workbook, sFile As String 
     
    sFile = Dir(sPath & "*.xls") 
     'Loop through all .xls-Files in that path
    Do While sFile <> "" 
         
        Set Wb = Workbooks.Open(sPath & sFile) 
         
         'Do something with that Workbook, insert whatever you want to do here
        Debug.Print Wb.Name 
         'You can save it, if you like, here it's not saved
        Wb.Close False 
         
        sFile = Dir 
    Loop 
End Sub

How to use:

  1. Copy the code above.
  2. Open your workbook and hit Alt+F11 to open the VB Editor (VBE).
  3. From the menu, choose Insert-Module.
  4. Paste the code into the code area of the module at right.
  5. Close the VBE.
 

Test the code:

  1. Place some Excel files together with other stuff in a directory.
  2. Make sure none of the Excel files is password protected (though this code can be altered to allow for it--just ask at our forum).
  3. Go to Tools-Macro-Macros and double-click on ProcessAll.
 

Sample File:

No Attachment 

Approved by Anne Troy


This entry has been viewed 314 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express