Solved: Open Excel document from Word

[mvesaas]

Okay, here is another one. :*)

I am working on closing out all the pages of my project. There are 5 open documents by the time all of my macros have run. Since this is a mail merge project, there are Excel data sources open when the final Word doc has been completed.

What I have done so far, is written code to close out 3 word docs, dont save them, and then prompt the user to save the final version.

Problem is that the Mail Merge data sources .xls are coming up inbetween and prompting to be saved or not. Is there anyway I can Activate EXCEL with specific workbook names, then close the Excel workbooks, without saving?? :whistle:

("MERGE Data.xls")("MERGE 2nd Data.xls")("MERGE 3rd Data.xls) are the excel data sources that remain open at the end of my code.

See the code below I am using to close out the WORD docs.


'Take the final document, remove the merge capability, and SAVE AS...

Windows("Form Letters3").Activate
With Dialogs(wdDialogFileSaveAs)
.Name = "c:\windows\temp\"
.Show
End With

'Close out the other open document - Dont save

Windows("Document1").Activate
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
ActiveDocument.Close SaveChanges:=False

Windows("Form Letters1").Activate
ActiveDocument.Close SaveChanges:=False

Windows("Form Letters2").Activate
ActiveDocument.Close SaveChanges:=False

[MOS MASTER]

I'm still waiting for a reply on your previous question. :*)

[Jacob Hilderbrand]

You can use something like this.


Option Explicit

Sub Macro1()

Dim AppExcel As Object
Dim Wkb As Object

On Error Resume Next
Set AppExcel = GetObject(, "Excel.Application")
Set Wkb = AppExcel.workbooks("Book1.xls")
On Error GoTo 0

If AppExcel Is Nothing Then
MsgBox "Excel is not open."
GoTo Canceled:
End If

If Wkb Is Nothing Then
MsgBox "The workbook is not open."
Else
Wkb.Close SaveChanges:=False
End If

Canceled:

Set Wkb = Nothing
Set AppExcel = Nothing

End Sub

[mvesaas]

:hi:

Okay great, this works and does exactly what I need it to.

Very nice - Thanks for your help!