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.
Contents
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
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:
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:
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:
- Word VBA, Open Document
- Word Automation VBA, Common Errors
- Word VBA, Modify Header For Multiple Files
- Word Automation VBA, Common Errors
- VBA, Write Excel Values to Word Document
- VBA, Automating Word From Excel
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
2 thoughts on “Word VBA, Apply Macro to Multiple Files”