How to Open Word Document and Save As PDF or Docx with VBA Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

The Word File Dataset to Open Word Document and Save As PDF or Docx with VBA Excel

This is a word file namedStudent 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.

Storing values in worksheet to Open Word Document and Save As PDF or Docx with VBA Excel

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 Sub

Your code is now ready to run.

Open Word Document and Save As New Docx with VBA Excel

  • 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 namedStudent 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.

Result of Open Word Document and Save As New Docx with VBA Excel

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 newStudent 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.Document

Then, declare the necessary variables for the macro.

Set iApp = CreateObject("Word.Application")
iApp.Visible = True

Next, 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:=False

Then, specify the output file name along with the new path location and the file type.

End With

After performing the find and replace operation, leave the source word file.

End Sub

Finally, 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 namedStudent 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 Sub

Your code is now ready to run.

Open Word Document and Save As PDF with VBA Excel

  • After that, Run the macro as we showed you in the above section.
  • You will notice that the new PDF file namedStudent 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.

Result of Open Word Document and Save As PDF with VBA Excel

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 newStudent Information FilePDF 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.Document

Then, declare the necessary variables for the macro.

Set iApp = CreateObject("Word.Application")
iApp.Visible = True

Next, 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:=False

Then, specify the output file name along with the new path location and the file type.

End With

After performing the find and replace operation, leave the source word file.

iApp.Quit
Set iDoc = Nothing
Set iApp = Nothing

As 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 Sub

Finally, 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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo