How to Open a Word document with Excel VBA

This post will show you how to open a word document with Excel VBA. This is a great technique for Excel developers building dashboards or reports when you want to open a specific word document containing additional notes.

Table of Contents

Introduction

Excel VBA can help you automate the creation of Excel dashboards and reports but it can do almost anything you want, including opening up documents from other software sources like Microsoft Word.

A great example of why you might want to use open up a Word document with Excel VBA is when you are creating Excel dashboards that have additional commentary or notes contained in Word, or perhaps more detailed guidance to match your dashboard.

To begin with you need to be in the Excel VBA editor. You can do this by selecting Developer > Visual Basic on the Excel ribbon or press ALT and F11 (pressed together) to use a shortcut command. If you can’t see the Developer tab on your version of Excel then please review our post on how to add the Developer tab to Excel.

With the Visual Basic Editor window now insert a new module by clicking Insert then Module from the drop down menu bar at the top of the window.

This enables a new code window where we can include the VBA code required to open a word document from Excel.

In the code window insert the following script, remember to change the INSERT FILE NAME section to the full path and document name of your word document. You must include the file type at the end so for word this will either be .doc or .docx depending on what version of word you use.

For example if your word document is stored in C:\MyDocuments and called MyWordDocument then you would replace INSERT FILE NAME with “C:\MyDocuments\MyWordDocument.docx”

Sub OpnWord()
    Dim oAPP As Object
    Dim filePath As String

    ' Specify the path to the Word document
    filePath = "INSERT FULL PATH TO YOUR WORD DOCUMENT HERE"

    ' Create a new Word application
    Set oAPP = CreateObject(Class:="Word.Application")

    ' Check if the file exists
    If Dir(filePath) <> "" Then
        ' Make Word visible
        oAPP.Visible = True

        ' Open the document
        oAPP.Documents.Open Filename:=filePath
    Else
        MsgBox "File not found: " & filePath, vbExclamation, "Error"
    End If

    ' Error handling
    On Error GoTo ErrorHandler
    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical, "Error"
End Sub

Next save the Excel file (remember to save as a Macro-Enabled file) and you now have a script that will open up the specified word document when it is run.

Understanding the VBA code

When you open a Word document with Excel VBA each line of code serves a specific function. Comprehending what each line does will assist you in modifying the VBA code if necessary.

Initiating and Setting Up the Procedure

• Sub OpnWord(): Begins the VBA procedure. You can rename “OpnWord” to something else if preferred.
  
• Dim oAPP as Object, Dim filePath As String: These lines declare two variables. “oAPP” will be used as an object representing the Word application, and “filePath” is a string that will hold the full path to the Word document.

Configuring and Opening the Word Application

• Set oAPP = CreateObject(Class:=”Word.Application”): Defines “oAPP” as the Microsoft Word application.
• oAPP.Visible = True: Makes the Word application visible to the user.

File Handling and Error Checking

• If Dir(filePath) <> “” Then: Checks if the Word document exists at the specified ‘filePath’. If it exists, the script proceeds to open the document.
  
• oAPP.Documents.Open Filename:=filePath: Opens the Word document specified in the ‘filePath‘.
  
• Else: If the file doesn’t exist, shows a message box indicating the file wasn’t found.
  
• MsgBox “File not found: ” & filePath, vbExclamation, “Error”: Displays an error message with the intended file path.
  
• End If: Concludes the ‘If’ statement for file existence checking.

Error Handling

• On Error GoTo ErrorHandler: Directs to the error handling section if an error occurs.
  
• Exit Sub: Exits the subroutine if there are no errors.
  
• ErrorHandler:
  • MsgBox “An error occurred: ” & Err.Description, vbCritical, “Error”: Displays an error message if any error occurs during the execution of the script.
    
• End Sub: Marks the end of the VBA script.

Practical Applications of the Excel-to-Word VBA Script

There are multiple applications for this script, a few worth mentioning are:

1. Easy Document Access in Excel Dashboards: Integrate the script with buttons in your Excel dashboard. This lets users open specific Word documents directly from Excel with a single click.
   
2. Basis for Automated Reporting: Use this script as a starting point to automate the creation of Word reports with data from Excel. It’s ideal for automating management reports, saving time and effort.
   
3. Customizable and Synchronized Reports: Tailor Word documents to include charts and data from Excel, ensuring reports are up-to-date and in line with your needs.

Summary

In conclusion, this Excel VBA script is a powerful tool for automating the opening of Word documents directly from an Excel dashboard. By integrating this script, you can enhance your reports and dashboards, offering a seamless user experience.

Furthermore, this script lays the groundwork for more advanced automation, such as populating Word documents with Excel data, making it an invaluable asset for efficient management reporting.

Embrace this technique to streamline your workflow and unlock new potentials in Excel VBA.

Keep Excelling,

After mastering how to open a Word document with Excel VBA, why not enhance your Excel skills further? Check out our next post on ‘How to Add a Trendline in Excel.’

It’s a perfect next step for anyone looking to deepen their understanding of Excel’s powerful data analysis tools. Dive into this guide to learn how to effectively use trendlines and elevate your data presentation game!