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.
--
Confused, but learning.

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


--
Cheers
Nigel



"Canoeist" wrote in message
...
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.
--
Confused, but learning.

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" wrote:

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


--
Cheers
Nigel



"Canoeist" wrote in message
...
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.
--
Confused, but learning.

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)


--
Cheers
Nigel



"Canoeist" wrote in message
...
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" wrote:

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


--
Cheers
Nigel



"Canoeist" wrote in message
...
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.
--
Confused, but learning.