Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel won't quit
I've got this app that's driving me nuts. Excel is
automating Word to do a mail merge. The problem is after doing the merge and quitting excel, the task manager show excel still running in the back ground. Can one of you wonder Office guru's point me in the right direction. Here is what I've pretty much proven to myself. The excel file has a bunch of forms and only 2 sheets. If I don't do the mail merge there is no problem when quitting excel. So I'm sure the problem lies in the mail merge somewhere. If I use my normal code to open word and a non mail merge doc, there is no problem. If only happens when I do the merge. Here is what I think is the possible code problem areas. In each word mail merge doc that excel opens the following code is in the doc open. (I did this so they can look at any of the docs without having to go through my code.) Private Sub Document_Open() ActiveDocument.MailMerge.MainDocumentType = wdFormLetters ActiveDocument.MailMerge.OpenDataSource Name:= _ "C:\mydir\mydata.xls" _ , ConfirmConversions:=False, ReadOnly:=True, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us e r ID=Admin;Data Source=C:\mydir\mydata.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet O" _ , SQLStatement:="SELECT * FROM `PrintOut`", SQLStatement1:="", _ OpenExclusive:=False, SubType:=wdMergeSubTypeAccess End Sub I've tried adding the following code to the word document close with no effect. Private Sub Document_Close() ActiveDocument.MailMerge.DataSource.Close End Sub Here is the excel code that launches word and automates it. I'm guessing that this is ok because if I have it run using a non-mail merge doc I don't see the problem. But at this point I'm really not sure. Sub StepOne() strpath = "C:\mydir\" ' 'Start Word Dim ox As Object Set ox = CreateObject("word.application") ox.Visible = True ox.Options.DefaultOpenFormat = 1 'Open MyMailmerge.com ox.Documents.Open Filename:=strpath + "mymailmerge.doc" 'ox.Documents.Open Filename:=strpath + "mynonmailmerge.doc" ox.Selection.WholeStory ox.Selection.Copy ox.activedocument.Close 'Open A new blank doc Set docNew = ox.Documents.Add tempstr = strpath + "mydata.xls" docNew.mailmerge.MainDocumentType = 0 'Set Data source in new doc docNew.MailMerge.OpenDataSource Name:="" + tempstr + "", _ ReadOnly:=True, _ Connection:= _ "Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=""DSN=Excel Files;DBQ=C:\mydir\mydata.xls;DefaultDir=C:\mydir; DriverId= 790;MaxBuf" _ , SQLStatement:="SELECT * FROM `PrintOut`", SQLStatement1:="" ' 'Paste Check set to new doc Set Range2 = docNew.Content Range2.Collapse Direction:=0 Range2.Paste With ox.activedocument.MailMerge .Destination = 1 .SuppressBlankLines = True With .DataSource .FirstRecord = 1 .LastRecord = -16 End With .Execute Pause:=False End With ox.activedocument.Close SaveChanges:=0 Set docNew = Nothing Set Range2 = Nothing ox.Quit Set ox = Nothing End Sub Here is my excel shut down code. Again I don't think this is the problem area because if word docs are not mail merge there is no problem. Sub MyShutdown() Application.DisplayAlerts = False Application.DisplayFormulaBar = True Application.CommandBars("Standard").Visible = True Application.CommandBars("Formatting").Visible = True Application.CommandBars("Cell").Reset If Worksheets("Main").FilterMode = True Then Selection.AutoFilter End If Cancel = False ActiveWorkbook.Save Application.Quit End Sub What the heck am I doing wrong. TIA Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can't quit excel | Excel Discussion (Misc queries) | |||
Excel instance won't quit | Excel Discussion (Misc queries) | |||
Excel won't quit | Excel Discussion (Misc queries) | |||
Excel Quit opening | Excel Discussion (Misc queries) | |||
quit excel with macro | Excel Programming |