Implementing VBA macro is the most effective, quickest and safest method to run any operation in Excel. This article will show you how to open a word document, modify if needed and save it as a PDF file or a new updated word file (.docx) with VBA in Excel.
Implementations of Excel VBA to Open Word Document and Save As PDF or Docx
Consider the following image of a word file.
This is a word file named “Student Information” stored in our computer system at C:\ExcelDemy\ location. What we are going to do with VBA code is, we will open this word file, modify the data if we have to and then save it as a new Word file or PDF file in our computer system.
We will update the two names indicated in the above picture as an example for the sake of this article.
Suppose we want to update the student’s name “Dean Ambrose” to “Jon Moxley” and “Danial Bryan” to “Bryan Danielson”. So, let’s store those two updated names in our Excel worksheet.
As you can see from the image above, we stored “Jon Moxley” in Cell C6 and “Bryan Danielson” in Cell C7.
Now we will learn how to replace the old student information – “Dean Ambrose” and “Danial Bryan” – with the new ones – “Jon Moxley” and “Bryan Danielson” – and save the modified file as a new Word file or PDF file with the VBA code in Excel.
1. Embed VBA to Open the Word Document and Save As a New Updated Word File
The steps to open the “Student Information” word document, modify the information and save it as a new word file named “Student Information File” with VBA in Excel are given below.
Steps:
- In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
- Next, in the pop-up code window, from the menu bar, click Insert -> Module.
- Then, copy the following code and paste it into the code window.
Sub OpenWordAndSaveAs()
Dim iApp As Word.Application
Dim iDoc As Word.Document
Set iApp = CreateObject("Word.Application")
iApp.Visible = True
Set iDoc = iApp.Documents.Add(Template:="C:\ExcelDemy\Student Information.docx", NewTemplate:=False, DocumentType:=0)
With iDoc
.Application.Selection.Find.Text = "Dean Ambrose"
.Application.Selection.Find.Execute
.Application.Selection = Range("C6")
.Application.Selection.EndOf
.Application.Selection.Find.Text = "Danial Bryan"
.Application.Selection.Find.Execute
.Application.Selection = Range("C7")
.SaveAs2 Filename:=("C:\ExcelDemy\Student Information File"), FileFormat:=wdFormatXMLDocument, AddtoRecentFiles:=False
End With
End SubYour code is now ready to run.
- Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.
- After the successful code execution, there will be a new word file named “Student Information File” opened up automatically at the location provided in the code (in our case, we saved the file at “C:\ExcelDemy\” location). You can change the new file path from the opened word file too if you want to.
And as you can see from the above image, the old student information is now updated; “Dean Ambrose” becomes “Jon Moxley” and “Danial Bryan” becomes “Bryan Danielson” and saved as the new “Student Information File” Word file.
VBA Code Explanation
Sub OpenWordAndSaveAs()First, provide a name for the sub-procedure of the macro.
Dim iApp As Word.Application
Dim iDoc As Word.DocumentThen, declare the necessary variables for the macro.
Set iApp = CreateObject("Word.Application")
iApp.Visible = TrueNext, create the Word application function to return the Word.application object.
Set iDoc = iApp.Documents.Add(Template:="C:\ExcelDemy\Student Information.docx", NewTemplate:=False, DocumentType:=0)After that, specify the source word file along with the path.
With iDoc
.Application.Selection.Find.Text = "Dean Ambrose"
.Application.Selection.Find.Execute
.Application.Selection = Range("C6")
.Application.Selection.EndOf
.Application.Selection.Find.Text = "Danial Bryan"
.Application.Selection.Find.Execute
.Application.Selection = Range("C7")Later, specify which word to find in the word document and what to replace it with. For instance, we wanted to find the words “Dean Ambrose” and “Danial Bryan” in the source word file and replace those with the values from the cells C6 and C7 respectively.
.SaveAs2 Filename:=("C:\ExcelDemy\Student Information File"), FileFormat:=wdFormatXMLDocument, AddtoRecentFiles:=FalseThen, specify the output file name along with the new path location and the file type.
End WithAfter performing the find and replace operation, leave the source word file.
End SubFinally, end the sub-procedure of the macro.
Read More: Excel VBA: Open Word Document and Paste
2. Implement Macro to Open the Word Document and Save As PDF in Excel
From this section, you will learn how to open the “Student Information” word document, modify the information and save it as a PDF file named “Student Information File” with VBA in Excel. The steps to execute that are shown below.
Steps:
- Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- Then, copy the following code and paste it into the code window.
Sub OpenWordAndSaveAsPdf()
Dim iApp As Word.Application
Dim iDoc As Word.Document
Set iApp = CreateObject("Word.Application")
iApp.Visible = True
Set iDoc = iApp.Documents.Add(Template:="C:\ExcelDemy\Student Information.docx", NewTemplate:=False, DocumentType:=0)
With iDoc
.Application.Selection.Find.Text = "Dean Ambrose"
.Application.Selection.Find.Execute
.Application.Selection = Range("C6")
.Application.Selection.EndOf
.Application.Selection.Find.Text = "Danial Bryan"
.Application.Selection.Find.Execute
.Application.Selection = Range("C7")
.SaveAs2 Filename:=("C:\ExcelDemy\Student Information File"), FileFormat:=wdFormatPDF, AddtoRecentFiles:=False
End With
iApp.Quit
Set iDoc = Nothing
Set iApp = Nothing
End SubYour code is now ready to run.
- After that, Run the macro as we showed you in the above section.
- You will notice that the new PDF file named “Student Information File” is created in the provided path (“C:\ExcelDemy\”) in the code. Now open the PDF file to check whether the file has the updated student information or not.
As you can see from the image above, the old student information is now updated; “Dean Ambrose” becomes “Jon Moxley” and “Danial Bryan” becomes “Bryan Danielson” and saved as the new “Student Information File” PDF file.
VBA Code Explanation
Sub OpenWordAndSaveAsPdf()First, provide a name for the sub-procedure of the macro.
Dim iApp As Word.Application
Dim iDoc As Word.DocumentThen, declare the necessary variables for the macro.
Set iApp = CreateObject("Word.Application")
iApp.Visible = TrueNext, create the Word application function to return the Word.application object.
Set iDoc = iApp.Documents.Add(Template:="C:\ExcelDemy\Student Information.docx", NewTemplate:=False, DocumentType:=0)After that, specify the source word file along with the path.
With iDoc
.Application.Selection.Find.Text = "Dean Ambrose"
.Application.Selection.Find.Execute
.Application.Selection = Range("C6")
.Application.Selection.EndOf
.Application.Selection.Find.Text = "Danial Bryan"
.Application.Selection.Find.Execute
.Application.Selection = Range("C7")Later, specify which word to find in the word document and what to replace it with. For instance, we wanted to find the words “Dean Ambrose” and “Danial Bryan” in the source word file and replace those with the values from the cells C6 and C7 respectively.
.SaveAs2 Filename:=("C:\ExcelDemy\Student Information File"), FileFormat:=wdFormatPDF, AddtoRecentFiles:=FalseThen, specify the output file name along with the new path location and the file type.
End WithAfter performing the find and replace operation, leave the source word file.
iApp.Quit
Set iDoc = Nothing
Set iApp = NothingAs we wanted the PDF file as our outcome so we execute these lines just to close the word application and the word document. If you want to keep the word file open, then skip these lines.
End SubFinally, end the sub-procedure of the macro.
Read More: Excel VBA: Open Word Document and Replace Text
Key Point to Remember
When you work with the Word Application Object in your VBA code then you must do the following modification in your VBA code window.
- First, go to the Tools -> References… from the menu bar of the code window.
- Then, check the Microsoft Word XX Object Library box. Here, XX is the word version according to your system.
- Later, click OK.
Otherwise, you will get errors if you don’t turn on the Microsoft Word Object Library while working with the Word Application Object in Excel VBA.
Download Files
You can download the free practice Excel workbook and the practice Word File template from here.
Download Excel Workbook:
Download Word File:
Conclusion
This article showed you how to open a word document, modify if needed and save it as a PDF file or a new updated word file (.docx) with VBA in Excel. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.
