Word

Process All Documents in a Specified Directory Folder Including Subfolders

Ease of Use

Intermediate

Version tested with

Word XP and 2000 

Submitted by:

gilbar

Description:

Run your macro on all files in a specified folder. 

Discussion:

Let's say you have made a macro that works on the active document, and you need to run that macro on each document in a folder . Rather than manually opening each one and running your macro, these macros do all the repeats for you. 

Code:

instructions for use

			

Option Explicit Dim scrFso As Object 'a FileSystemObject Dim scrFolder As Object 'the folder object Dim scrSubFolders As Object 'the subfolders collection Dim scrFile As Object 'the file objectr Dim scrFiles As Object 'the files objectr Sub OpenAllFilesInFolder() 'starting place for trav macro 'strStartPath is a path to start the traversal on Dim strStartPath As String strStartPath = "C:\Afolder" 'stop the screen flickering Application.ScreenUpdating = False 'open the files in the start folder OpenAllFiles strStartPath 'search the subfolders for more files SearchSubFolders strStartPath 'turn updating back on Application.ScreenUpdating = True End Sub Sub SearchSubFolders(strStartPath As String) 'starts at path strStartPath and traverses its subfolders and files 'if there are files below it calls OpenAllFiles, which opens them one by one 'once its checked for files, it calls itself to check for subfolders. If scrFso Is Nothing Then Set scrFso = CreateObject("scripting.filesystemobject") Set scrFolder = scrFso.getfolder(strStartPath) Set scrSubFolders = scrFolder.subfolders For Each scrFolder In scrSubFolders Set scrFiles = scrFolder.Files If scrFiles.Count > 0 Then OpenAllFiles scrFolder.Path 'if there are files below, call openFiles to open them SearchSubFolders scrFolder.Path 'call ourselves to see if there are subfolders below Next End Sub _____________ Sub OpenAllFiles(strPath As String) ' runs through a folder oPath, opening each file in that folder, ' calling a macro called samp, and then closing each file in that folder Dim strName As String Dim wdDoc As Document If scrFso Is Nothing Then Set scrFso = CreateObject("scripting.filesystemobject") Set scrFolder = scrFso.getfolder(strPath) For Each scrFile In scrFolder.Files strName = scrFile.Name 'the name of this file Application.StatusBar = strPath & "\" & strName 'the status bar is just to let us know where we are 'we'll open the file fName if it is a word document or template If Right(strName, 4) = ".doc" Or Right(strName, 4) = ".dot" Then Set wdDoc = Documents.Open(FileName:=strPath & "\" & strName, _ ReadOnly:=False, Format:=wdOpenFormatAuto) 'Call the macro that performs work on the file pasing a reference to it DoWork wdDoc 'we close saving changes wdDoc.Close wdSaveChanges End If Next 'return control of status bar to Word Application.StatusBar = False End Sub 'this is where a macro would be that would actually do something Sub DoWork(wdDoc As Document) End Sub

How to use:

  1. Copy the code above.
  2. Open the Visual Basic Editor by pressing Alt+F11.
  3. Right click on your filename in the explorer tree on the left-hand side and select Insert-Module.
  4. Paste the code above into the right-hand code pane.
  5. Place the code of your macro that you want to act on each file in between the lines 'Sub DoWork(wdDoc As Document)' and 'End Sub'.
  6. Replace "C:\Afolder" with the path to the folder you wish to act upon (don't forget the quotes!)
 

Test the code:

  1. Go to Tools-Macro-Macros and double-click OpenAllFilesInFolder.
  2. The status bar will display the current progress.
 

Sample File:

Sample.zip 9.97KB 

Approved by mdmackillop


This entry has been viewed 262 times.

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