If you need to use Excel VBA to open a Word document, or simply want to learn the concept, then read on to find out how, along with some useful tips on taking the script further…
A great example of why you might need to use VBA to open up a Word document from Excel is when you are creating Excel dashboards.
In order to create a slick, professional and user-friendly Excel Dashboard one of the features you can adopt is to place buttons on the Dashboard that users can click to either Navigate around the Dashboard or Navigate elsewhere, to a Word Document for example.
Open a Word document from Excel using VBA
To begin open up the Excel VBA Editor this can be done with the shortcut command ALT and F11 (pressed together) or using the Ribbon at the top of Excel select “Developer” then “Visual Basic”.
With the Visual Basic Editor window now open insert a new module by left-clicking “Insert” then “New Module” from the drop down menu bar at the top of the window.
In the code window insert the following script and make sure you insert the full path of your word document in-between the quote marks:
1 2 3 4 5 6 7 8 9 10 11 12 13
Sub OpnWord() Dim WordDoc As String Dim oAPP As Object Set oAPP = CreateObject(Class:="Word.Application") oAPP.Visible = True oAPP.Documents.Open Filename:="INSERT FILE NAME HERE" End Sub
Save the module and you now have a script that will open up the specified word document when run.
What does the script mean?
I’m sure a few readers like to take the script and investigate themselves but for those of you who would like to gain a better understanding let’s review each line of script:
- Sub OpnWord()
All this does is start and name the procedure, if you don’t like the name “OpnWord” then you can alter it here.
- Dim oAPP As Object
This tells Excel that the name “oAPP” will be an object when we define it. It is common practice in VBA scripts to take longer pieces of code and shorten them to a smaller name and it helps keep the script clear if you also define the type like we have here.
- Set oAPP = CreateObject(Class:=”Word.Application”)
Having just defined the type of “oAPP” we now tell Excel what oAPP is. oAPP refers to the line of script CreateObject(Class:=”Word.Application”) and that is telling Excel that oAPP is the application Word.
- oAPP.Visible = True
Once Excel knows that “oAPP” is the application Word we can tell Excel to open word by making it visible using this script. The benefit of defining oAPP as the line of code CreateObject(Class:=”Word.Application”) is shown here because rather than type all that out again we just start the line with “oAPP”.
- oAPP.Documents.Open Filename:=”INSERT FILE NAME HERE”
Now all that is left is to open the file itself. Again this shows the benefit of defining the name oAPP as it is used once more to tell Excel this part of code refers to Word, then next part “Documents.Open” is fairly self-explanatory and ensures Excel knows it is opening a word document. Finally we specify the name of the document to be opened.
- End Sub
This tells Excel the script has ended.
Uses for this script
Once you have created the macro (using the script above) it can be assigned to buttons or other objects usually found on an Excel dashboard, that way Word documents can be opened instantly for the user, although the uses do not stop there.
With the power to open a word document from Excel the script can be taken a stage further and can be the starting point in gathering data from Word documents or even outputting parts of Excel dashboards to a standard report you have within Word – there are many uses if you think creatively!