Word VBA, Apply Macro to Multiple Files

In this article I will explain how you can use VBA for word to open multiple files and modify them.


Step 1:

Put all the word documents that you want to modify in one folder. Note the file with the VBA code should be kept somewhere else.


Step 2:

Use an open folder dialog to get the folder path from the user. I’ve explained about open file dialogs in the article below:

The code below will display an open folder dialog and request the user select a folder:

Sub Example1()
Dim intResult As Integer
Dim strPath As String
'the dialog is displayed to the user
intResult = Application.FileDialog(msoFileDialogFolderPicker).Show
'checks if user has cancled the dialog
If intResult <> 0 Then
'dispaly message box
strPath = Application.FileDialog( _
msoFileDialogFolderPicker).SelectedItems(1)
End If
End Sub

Word VBA, Folder Dialog
If the user selects a folder, the path will be stored in the variable strPath.


Step 3:

Get the list of all the file paths in the folder. I have explained how this can be done in the article below:

Although that article was aimed for Excel, the concept can also be applied to VBA for Word. The function below recieves as input the path of the folder. It returns a string array with the path of all the files in the folder.

Private Function GetAllFilePaths(ByVal strPath As String) As String()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
Dim arrOutput() As String
ReDim arrOutput(1 To 1)
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder(strPath)
i = 1
'loops through each file in the directory and prints
'their names and path
For Each objFile In objFolder.Files
ReDim Preserve arrOutput(1 To i)
'print file path
arrOutput(i) = objFile.Path
i = i + 1
Next objFile
GetAllFilePaths = arrOutput
End Function

Assume we have the following files in a folder:

Word VBA, Files in Folder
The code below uses the function above to get all the files in the folder:

Sub Example2()
Dim strPath As String
Dim arrFiles() As String

strPath = "D:StuffBusinessTemp"
arrFiles = GetAllFilePaths(strPath)

End Sub

Result:

Word, VBA, Get Files in Folder Result


Step 4:

The function below recieves as input the path of a word document, opens it, clears all the data and prints the text string “some data” in the document:

Private Sub ModifyFile(ByVal strPath As String)
Dim objDocument As Document
Set objDocument = Documents.Open(strPath)
objDocument.Activate
Selection.WholeStory
Selection.Delete
Selection.Range.Text = "Some Data"
objDocument.Close (True)
End Sub

This was only for illustrative purposes. You could apply any other modification required.


Complete Code:

Below you can see the complete code:

Sub Example1()
Dim intResult As Integer
Dim strPath As String
Dim arrFiles() As String
Dim i As Integer
'the dialog is displayed to the user
intResult = Application.FileDialog(msoFileDialogFolderPicker).Show
'checks if user has cancled the dialog
If intResult <> 0 Then
'dispaly message box
strPath = Application.FileDialog( _
msoFileDialogFolderPicker).SelectedItems(1)
arrFiles() = GetAllFilePaths(strPath)
For i = LBound(arrFiles) To UBound(arrFiles)
Call ModifyFile(arrFiles(i))
Next i
End If
End Sub

Private Sub ModifyFile(ByVal strPath As String)
Dim objDocument As Document
Set objDocument = Documents.Open(strPath)
objDocument.Activate
Selection.WholeStory
Selection.Delete
Selection.Range.Text = "Some Data"
objDocument.Close (True)
End Sub

Private Function GetAllFilePaths(ByVal strPath As String) _
As String()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
Dim arrOutput() As String
ReDim arrOutput(1 To 1)
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder(strPath)
i = 1
'loops through each file in the directory and
'prints their names and path
For Each objFile In objFolder.Files
ReDim Preserve arrOutput(1 To i)
'print file path
arrOutput(i) = objFile.Path
i = i + 1
Next objFile
GetAllFilePaths = arrOutput
End Function

You can download the file and code related to this article from the link below:

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *

privacy policy