Integrating Word and Excel



I have an Excel based user friendly application that processes a dataset
for use as source data for a mail merge document in Word.

To maintain the user friendliness (ie as little user knowledge and
interaction required) I am wondering how, if at all, I can accomplish
the following:

1) I can launch word and the Word merge document from excel, but how can
I return the user from Word back to Excel once the mail merge document
has been prepared? (without needing the user to actively participate in
closing windows and maneuvering others.

2) Automatically prepare the mail merge document, skipping the "Main
Document Setup". ie an Excel button launches the mail merge document
based on the criteria saved with the document and goes straight into the
final document preparation. No need for the user to press the "Merge to
new document" button.

3) Automatically assign the newly created document a file name and save
it after review by the user.

4) Return the user back to Excel where they left it (which may be in the
middle of a sub?)


Graham Mayor

There are umpteen web references demonstrating how to control Word from vba
in Excel e.g.

Sub CreateNewWordDoc()
' to test this code, paste it into an Excel module
' add a reference to the Word-library
' create a new folder named C:\Foldername or edit the filenames in the code
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim i As Integer
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Add ' create a new document
' or
'Set wrdDoc = wrdApp.Documents.Open("C:\Foldername\Filename.doc")
' open an existing document
' example word operations
With wrdDoc
'do what you want with the Word document here
'then save it.
.SaveAs ("C:\Foldername\MyNewWordDoc.doc")
.Close ' close the document
End With
wrdApp.Quit ' close the Word application
Set wrdDoc = Nothing
Set wrdApp = Nothing
End Sub

However, Excel vba questions should be addressed to an Excel vba forum

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question