Open a specific Word document from an Excel Macro

[Guest]

I am trying to use Word documents for mail merges that differ depending upon
the state to which they will be sent. I currently select the state in an
Excel worksheet from a combo box that translates the state abbreviation to a
number from 1 to 50. I use a vlookup function to convert the chosen state to
the appropriate file and path name. I need to know how to use the results of
the vlookup cell to open the indicated Word document.

 

[Nigel]

This is a GP macro for opening a word app and document, substitute your path
and filename where shown. You need to set a reference in Excel to the Word
Object Libary.

Sub OpenWord()
On Error GoTo errorHandler
Dim wordApp As Word.Application
Dim wordDoc As Word.Document
Set wordApp = New Word.Application
wordApp.Visible = True
wordApp.WindowState = wdWindowStateMaximize

Set wordDoc = wordApp.Documents.Open("C:\path\filename.doc")

wordApp.Quit
errorHandler:
Set wordApp = Nothing
Set wordDoc = Nothing
End Sub

 

[Guest]

Nigel,
Thanks much for your rapid response. Your answer doesn't do what I want it
to do, so I can only conclude that my question wasn't clear.

I have a cell in my excel worksheet that contains the file name I want to
open. I currently have a macro that opens Word with the statement:

Sub Open Word ()
Application.ActivateMicrosoftApp (x1MicrosoftWord)
End Sub

The results from my macro are unsatisfactory because word opens to a blank
document. Your macro requires me to include the Word document name and path
in the macro itself. What I would like is for the macro to pick up whatever
file name and path is currently in the cell. The cell entry is the result of
a lookup formula, since I have the user select one of fifty choices, each
having a related Word document. If it would help clarify the question, I
could include a sample Excel workbook.

Thanks

 

[Nigel]

Substitute in the line that reads....

Set wordDoc = wordApp.Documents.Open("C:\path\filename.doc")

with

Set wordDoc = wordApp.Documents.Open(Range.("A1").value)

where A1 or (whatever) is the cell in which the path and filename resides.

or put a variable in and assign the cell to the variable eg

Dim myDoc as String
myDoc = Range("A1").value

then use (to open it)

Set wordDoc = wordApp.Documents.Open(myDoc)