Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mail merge leaves excel process running
Hi,
I amtrying to mail merge information from a worksheet into a mail merge word document. Everything works fine, except it laves a process running once I quit excel. The mail merge is called from the same file as the informationto merge and excel is closed manually by the user. The code works correctly in 07 (well no process I can see is left), but it needs to run in excel 2003 and this is where the left over process is happening. the code is as follows; Sub print_Click() Dim FName As String Dim appWD As Object ' Word.Application Dim UserN As String Dim DesktopPath As String On Error Resume Next Application.StatusBar = "Starting mail merge ..." FName = Dir(ThisWorkbook.Path & "\addresses.xls") If appWD Is Nothing Then Set appWD = CreateObject("Word.Application") ' New Word.Application End If appWD.Documents.Open Filename:=ThisWorkbook.Path & "\label merge-auto.doc" With appWD.ActiveDocument.MailMerge .OpenDataSource Name:=ThisWorkbook.Path & "\" & FName, sqlstatement:="SELECT * FROM [print list$]" .Destination = wdSendToNewDocument .SuppressBlankLines = True .Execute Pause:=False End With Application.StatusBar = "Now creating document for " & Left(FName, Len(FName) - 4) appWD.ActiveDocument.SaveAs (ThisWorkbook.Path & "\merged" & Format(Date, "dd-mm-yyyy") & "-week" & _ Worksheets("label order").Range("Q4").Value), FileFormat:=wdFormatDocument appWD.ActiveDocument.Close appWD.Documents("label merge-auto.doc").Close savechanges:=False appWD.Quit Set appWD = Nothing Application.StatusBar = False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mail merge leaves excel process running
You need to make sure that all files are closed other than the one containing the code. Otherwise VBA will not allow the application to close. It thinks you are still working. "Anthony" wrote in message ... Hi, I amtrying to mail merge information from a worksheet into a mail merge word document. Everything works fine, except it laves a process running once I quit excel. The mail merge is called from the same file as the informationto merge and excel is closed manually by the user. The code works correctly in 07 (well no process I can see is left), but it needs to run in excel 2003 and this is where the left over process is happening. the code is as follows; Sub print_Click() Dim FName As String Dim appWD As Object ' Word.Application Dim UserN As String Dim DesktopPath As String On Error Resume Next Application.StatusBar = "Starting mail merge ..." FName = Dir(ThisWorkbook.Path & "\addresses.xls") If appWD Is Nothing Then Set appWD = CreateObject("Word.Application") ' New Word.Application End If appWD.Documents.Open Filename:=ThisWorkbook.Path & "\label merge-auto.doc" With appWD.ActiveDocument.MailMerge .OpenDataSource Name:=ThisWorkbook.Path & "\" & FName, sqlstatement:="SELECT * FROM [print list$]" .Destination = wdSendToNewDocument .SuppressBlankLines = True .Execute Pause:=False End With Application.StatusBar = "Now creating document for " & Left(FName, Len(FName) - 4) appWD.ActiveDocument.SaveAs (ThisWorkbook.Path & "\merged" & Format(Date, "dd-mm-yyyy") & "-week" & _ Worksheets("label order").Range("Q4").Value), FileFormat:=wdFormatDocument appWD.ActiveDocument.Close appWD.Documents("label merge-auto.doc").Close savechanges:=False appWD.Quit Set appWD = Nothing Application.StatusBar = False End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mail merge leaves excel process running
The code closes the only file opened, which is the word document. and the
user closes the excel file. No files are left open, but the excel process still remains active. The workbook used as the data source is the boo k with the code in it. Regards Anthony "JLGWhiz" wrote in message ... You need to make sure that all files are closed other than the one containing the code. Otherwise VBA will not allow the application to close. It thinks you are still working. "Anthony" wrote in message ... Hi, I amtrying to mail merge information from a worksheet into a mail merge word document. Everything works fine, except it laves a process running once I quit excel. The mail merge is called from the same file as the informationto merge and excel is closed manually by the user. The code works correctly in 07 (well no process I can see is left), but it needs to run in excel 2003 and this is where the left over process is happening. the code is as follows; Sub print_Click() Dim FName As String Dim appWD As Object ' Word.Application Dim UserN As String Dim DesktopPath As String On Error Resume Next Application.StatusBar = "Starting mail merge ..." FName = Dir(ThisWorkbook.Path & "\addresses.xls") If appWD Is Nothing Then Set appWD = CreateObject("Word.Application") ' New Word.Application End If appWD.Documents.Open Filename:=ThisWorkbook.Path & "\label merge-auto.doc" With appWD.ActiveDocument.MailMerge .OpenDataSource Name:=ThisWorkbook.Path & "\" & FName, sqlstatement:="SELECT * FROM [print list$]" .Destination = wdSendToNewDocument .SuppressBlankLines = True .Execute Pause:=False End With Application.StatusBar = "Now creating document for " & Left(FName, Len(FName) - 4) appWD.ActiveDocument.SaveAs (ThisWorkbook.Path & "\merged" & Format(Date, "dd-mm-yyyy") & "-week" & _ Worksheets("label order").Range("Q4").Value), FileFormat:=wdFormatDocument appWD.ActiveDocument.Close appWD.Documents("label merge-auto.doc").Close savechanges:=False appWD.Quit Set appWD = Nothing Application.StatusBar = False End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mail merge leaves excel process running
Solved it, with a bit of a work around.
thisworkbook.savecopyas (file backup) link backup to mail merge kill backup Becasue the caling worbook is never linked it doesn leave anything in memory when it closes. Regards Anthony "Anthony" wrote in message ... The code closes the only file opened, which is the word document. and the user closes the excel file. No files are left open, but the excel process still remains active. The workbook used as the data source is the boo k with the code in it. Regards Anthony "JLGWhiz" wrote in message ... You need to make sure that all files are closed other than the one containing the code. Otherwise VBA will not allow the application to close. It thinks you are still working. "Anthony" wrote in message ... Hi, I amtrying to mail merge information from a worksheet into a mail merge word document. Everything works fine, except it laves a process running once I quit excel. The mail merge is called from the same file as the informationto merge and excel is closed manually by the user. The code works correctly in 07 (well no process I can see is left), but it needs to run in excel 2003 and this is where the left over process is happening. the code is as follows; Sub print_Click() Dim FName As String Dim appWD As Object ' Word.Application Dim UserN As String Dim DesktopPath As String On Error Resume Next Application.StatusBar = "Starting mail merge ..." FName = Dir(ThisWorkbook.Path & "\addresses.xls") If appWD Is Nothing Then Set appWD = CreateObject("Word.Application") ' New Word.Application End If appWD.Documents.Open Filename:=ThisWorkbook.Path & "\label merge-auto.doc" With appWD.ActiveDocument.MailMerge .OpenDataSource Name:=ThisWorkbook.Path & "\" & FName, sqlstatement:="SELECT * FROM [print list$]" .Destination = wdSendToNewDocument .SuppressBlankLines = True .Execute Pause:=False End With Application.StatusBar = "Now creating document for " & Left(FName, Len(FName) - 4) appWD.ActiveDocument.SaveAs (ThisWorkbook.Path & "\merged" & Format(Date, "dd-mm-yyyy") & "-week" & _ Worksheets("label order").Range("Q4").Value), FileFormat:=wdFormatDocument appWD.ActiveDocument.Close appWD.Documents("label merge-auto.doc").Close savechanges:=False appWD.Quit Set appWD = Nothing Application.StatusBar = False End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing leaves EXCEL.EXE process in Task Manager | Excel Discussion (Misc queries) | |||
How to count process running time ( process not finished) | Excel Programming | |||
How to count process running time ( process not finished) | Excel Programming | |||
Mail merge issue - ghost Excel process remains after closing application | Excel Programming | |||
Mail merge issue - ghost Excel process remains after closing application | Excel Programming |