Control Word from Excel: Open a Word Message Box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Daniel Van Eygen - 30 Nov 2004 09:59 GMT

I am writing VBA code in Excel that opens a Word doc from Excel, then pastes
Excel content into Word and formats that content. At the end of the ride the
Word doc is automatically saved using a file name that is created in Excel.
All this works well. However before saving I would like to give the user a
possibility to edit the Word document. Hereafter is a snippet of code
showing where the message box has to be inserted. Can anybody help me on my
way on how to get a MsgBox going in Word and take action based on the reply
(Yes, No or Finished).

savedoc:
With wrdDoc
...................
Open Msg box and ask if the user wants to edit the file or not. If the
answer is yes than the box needs to stay open until the user has done
editing and clicks "finished". If the answer is no then the MsgBox has to
disappear and the following code needs to run:
...................

oWord.ActiveDocument.SaveAs Filename:=(fullDocName)
oWord.Dialogs(wdDialogFileSaveAs).Show
oWord.ActiveDocument.Close
oWord.Quit
End With

Reply to this Message

Jezebel - 30 Nov 2004 11:27 GMT

First, why do you show the SaveAs dialog after you've saved the document? --
does the user need to save the document a second time, under a new name?

But to the main question, a simpler design would make your life easier. If
the user DOES want to edit the document, does your Excel code have to do
anything further? -- why not just pass control to the user and leave it at
that? If the user edits, they need to be free to save their changes or not,
as they choose.

savedoc:

:
'Save the document
oWord.ActiveDocument.SaveAs Filename:=(fullDocName)

'Ask the if the user wants to edit - if yes, they can do their own
closing and quitting
If msgbox("Do you want to edit the document?", vbYesNo) = vbYes then
oWord.Activate

'If no, close and quit
Else
oWord.ActiveDocument.Close
oWord.Quit
end if

> I am writing VBA code in Excel that opens a Word doc from Excel, then pastes
> Excel content into Word and formats that content. At the end of the ride the
[quoted text clipped - 19 lines]
> oWord.Quit
> End With

Reply to this Message

Daniel Van Eygen - 30 Nov 2004 12:14 GMT

Thanks a lot Jezebel. This is exactly what I needed.

> First, why do you show the SaveAs dialog after you've saved the document? --
> does the user need to save the document a second time, under a new name?
[quoted text clipped - 50 lines]
> > oWord.Quit
> > End With

Reply to this Message

Rate this thread: