Mach1pro
asked on
Instance of Excel still running as process in Task Manager
Below is my code to open a file in Word and then change it to Text only format, then reopen the file in Excel so that it can be manipulated. For some reason after my code runs,there is still an instance of Excel running in memory. I know it's some simple command I'm overlooking, I just can't find it.
''''''''''''''''''''''''''
Dim objWord As Object
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet
Dim strTemp As String
Dim strCust As String
Dim strNew As String
Dim strExcel As String
Dim intResponse As Integer
Dim iFile As Integer
strTemp = rtfText.FileName
strNew = App.Path & "\RPC_Design_Results.doc"
iFile = FreeFile
Screen.MousePointer = vbHourglass
Open strNew For Output As #iFile
Print #iFile, rtfText.TextRTF
Close #iFile
Set objWord = CreateObject("Word.Applica
Set wd = objWord.documents.Open(str
objWord.Application.Visibl
wd.SaveAs "c:\tmp_repdoc.txt", 2
wd.Close
objWord.Quit
Set wd = Nothing
Set objWord = Nothing
strExcel = App.Path & "\Rpc_Design_Results.xls"
If Dir(strExcel) <> "" Then
Kill strExcel
End If
Set xl = New Excel.Application
xl.Workbooks.OpenText FileName:="c:\tmp_repdoc.t
, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(8, 1) _
, Array(12, 1), Array(21, 1), Array(32, 1), Array(40, 1), Array(50, 1), Array(58, 1), Array( _
65, 1), Array(72, 1))
Set wb = xl.Workbooks(1)
wb.SaveAs FileName:=strExcel, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False
, CreateBackup:=False
Set sh = wb.Sheets(1)
sh.Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
sh.Range("H8:J8").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
sh.Cells(1, 1) = strCustomerString
sh.Cells(7, 2) = "Material"
wb.Save
Set sh = Nothing
wb.Close
xl.Quit
Set wb = Nothing
Set xl = Nothing
Screen.MousePointer = vbDefault
SendEmail strExcel, "Design Results", , strCust
''''''''''''''''''''''''''
Dim objWord As Object
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet
Dim strTemp As String
Dim strCust As String
Dim strNew As String
Dim strExcel As String
Dim intResponse As Integer
Dim iFile As Integer
strTemp = rtfText.FileName
strNew = App.Path & "\RPC_Design_Results.doc"
iFile = FreeFile
Screen.MousePointer = vbHourglass
Open strNew For Output As #iFile
Print #iFile, rtfText.TextRTF
Close #iFile
Set objWord = CreateObject("Word.Applica
Set wd = objWord.documents.Open(str
objWord.Application.Visibl
wd.SaveAs "c:\tmp_repdoc.txt", 2
wd.Close
objWord.Quit
Set wd = Nothing
Set objWord = Nothing
strExcel = App.Path & "\Rpc_Design_Results.xls"
If Dir(strExcel) <> "" Then
Kill strExcel
End If
Set xl = New Excel.Application
xl.Workbooks.OpenText FileName:="c:\tmp_repdoc.t
, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(8, 1) _
, Array(12, 1), Array(21, 1), Array(32, 1), Array(40, 1), Array(50, 1), Array(58, 1), Array( _
65, 1), Array(72, 1))
Set wb = xl.Workbooks(1)
wb.SaveAs FileName:=strExcel, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False
, CreateBackup:=False
Set sh = wb.Sheets(1)
sh.Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
sh.Range("H8:J8").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
sh.Cells(1, 1) = strCustomerString
sh.Cells(7, 2) = "Material"
wb.Save
Set sh = Nothing
wb.Close
xl.Quit
Set wb = Nothing
Set xl = Nothing
Screen.MousePointer = vbDefault
SendEmail strExcel, "Design Results", , strCust
I think the other way to close excel is xl.Application.Quit
though I dont expect it makes much difference
though I dont expect it makes much difference
Hi,
Try adding an xl.Visible = True line near the start of your Excel processing and see if there are dialog boxes being put up by the application that you can't see and which prevent it from closing.
HTH
Rory
Try adding an xl.Visible = True line near the start of your Excel processing and see if there are dialog boxes being put up by the application that you can't see and which prevent it from closing.
HTH
Rory
ASKER
so far these ideas haven't worked. I didn't use the late binding on Excel because I could never get it do the import and save of the text file properly, that's why I set a reference to Excel and that part of the code worked fine then.
When I close my exe file, the Excel process is going away. I didn't find anywhere else in my code that I declared xl as as variable. I thought that might have been the problem. So I still need some help here.
When I close my exe file, the Excel process is going away. I didn't find anywhere else in my code that I declared xl as as variable. I thought that might have been the problem. So I still need some help here.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi ,
Killing it is the only solution cos i tried all the above ideas...
Do Following 2 steps
1) Paste following code into a module .
Public Function EndAllInstances(ByVal WindowCaption As String) _
As Boolean
'PURPOSE: ENDS ALL RUNNING INSTANCES OF A PROCESS
'THAT CONTAINS ANY PART OF THE WINDOW CAPTION
'INPUT: ANY PART OF THE WINDOW CAPTION
'RETURNS: TRUE IF SUCCESSFUL (AT LEASE ONE PROCESS WAS STOPPED,
'FALSE OTHERWISE)
'EXAMPLE EndProcess "Notepad"
'NOTES:
'1. THIS IS DESIGNED TO TERMINATE THE PROCESS IMMEDIATELY,
' THE APP WILL NOT RUN THROUGH IT'S NORMAL SHUTDOWN PROCEDURES
' E.G., THERE WILL BE NO DIALOG BOXES LIKE "ARE YOU SURE
' YOU WANT TO QUIT"
'2. BE CAREFUL WHEN USING:
' E.G., IF YOU CALL ENDPROCESS("A"), ANY PROCESS WITH A
' WINDOW THAT HAS THE LETTER "A" IN ITS CAPTION WILL BE
' TERMINATED
'3. AS WRITTEN, ALL THIS CODE MUST BE PLACED WITHIN
' A FORM MODULE
Dim hwnd As Long
Dim hInst As Long
Dim hProcess As Long
Dim lProcessID
Dim bAns As Boolean
Dim lExitCode As Long
Dim lRet As Long
On Error GoTo ErrorHandler
If Trim(WindowCaption) = "" Then Exit Function
Do
hwnd = FindWin(WindowCaption)
If hwnd = 0 Then Exit Do
hInst = GetWindowThreadProcessId(h
'Get handle to process
hProcess = OpenProcess(PROCESS_ALL_AC
If hProcess <> 0 Then
'get exit code
GetExitCodeProcess hProcess, lExitCode
If lExitCode <> 0 Then
'bye-bye
lRet = TerminateProcess(hProcess,
If bAns = False Then bAns = lRet > 0
If bAns = True Then Exit Do
End If
End If
Loop
EndAllInstances = bAns
ErrorHandler:
End Function
Private Function FindWin(WinTitle As String) As Long
Dim lhWnd As Long, sAns As String
Dim sTitle As String
lhWnd = Me.hwnd
sTitle = LCase(WinTitle)
Do
DoEvents
If lhWnd = 0 Then Exit Do
sAns = LCase$(GetCaption(lhWnd))
If InStr(sAns, sTitle) Then
FindWin = lhWnd
Exit Do
Else
FindWin = 0
End If
lhWnd = GetNextWindow(lhWnd, 2)
Loop
End Function
Private Function GetCaption(lhWnd As Long) As String
Dim sAns As String, lLen As Long
lLen& = GetWindowTextLength(lhWnd)
sAns = String(lLen, 0)
Call GetWindowText(lhWnd, sAns, lLen + 1)
GetCaption = sAns
End Function
'''''*********************
2) Call the following function as follows after doing all ur processes
Call EndAllInstances ("Microsoft Excel")
be happy
-Dany
Killing it is the only solution cos i tried all the above ideas...
Do Following 2 steps
1) Paste following code into a module .
Public Function EndAllInstances(ByVal WindowCaption As String) _
As Boolean
'PURPOSE: ENDS ALL RUNNING INSTANCES OF A PROCESS
'THAT CONTAINS ANY PART OF THE WINDOW CAPTION
'INPUT: ANY PART OF THE WINDOW CAPTION
'RETURNS: TRUE IF SUCCESSFUL (AT LEASE ONE PROCESS WAS STOPPED,
'FALSE OTHERWISE)
'EXAMPLE EndProcess "Notepad"
'NOTES:
'1. THIS IS DESIGNED TO TERMINATE THE PROCESS IMMEDIATELY,
' THE APP WILL NOT RUN THROUGH IT'S NORMAL SHUTDOWN PROCEDURES
' E.G., THERE WILL BE NO DIALOG BOXES LIKE "ARE YOU SURE
' YOU WANT TO QUIT"
'2. BE CAREFUL WHEN USING:
' E.G., IF YOU CALL ENDPROCESS("A"), ANY PROCESS WITH A
' WINDOW THAT HAS THE LETTER "A" IN ITS CAPTION WILL BE
' TERMINATED
'3. AS WRITTEN, ALL THIS CODE MUST BE PLACED WITHIN
' A FORM MODULE
Dim hwnd As Long
Dim hInst As Long
Dim hProcess As Long
Dim lProcessID
Dim bAns As Boolean
Dim lExitCode As Long
Dim lRet As Long
On Error GoTo ErrorHandler
If Trim(WindowCaption) = "" Then Exit Function
Do
hwnd = FindWin(WindowCaption)
If hwnd = 0 Then Exit Do
hInst = GetWindowThreadProcessId(h
'Get handle to process
hProcess = OpenProcess(PROCESS_ALL_AC
If hProcess <> 0 Then
'get exit code
GetExitCodeProcess hProcess, lExitCode
If lExitCode <> 0 Then
'bye-bye
lRet = TerminateProcess(hProcess,
If bAns = False Then bAns = lRet > 0
If bAns = True Then Exit Do
End If
End If
Loop
EndAllInstances = bAns
ErrorHandler:
End Function
Private Function FindWin(WinTitle As String) As Long
Dim lhWnd As Long, sAns As String
Dim sTitle As String
lhWnd = Me.hwnd
sTitle = LCase(WinTitle)
Do
DoEvents
If lhWnd = 0 Then Exit Do
sAns = LCase$(GetCaption(lhWnd))
If InStr(sAns, sTitle) Then
FindWin = lhWnd
Exit Do
Else
FindWin = 0
End If
lhWnd = GetNextWindow(lhWnd, 2)
Loop
End Function
Private Function GetCaption(lhWnd As Long) As String
Dim sAns As String, lLen As Long
lLen& = GetWindowTextLength(lhWnd)
sAns = String(lLen, 0)
Call GetWindowText(lhWnd, sAns, lLen + 1)
GetCaption = sAns
End Function
'''''*********************
2) Call the following function as follows after doing all ur processes
Call EndAllInstances ("Microsoft Excel")
be happy
-Dany
Also
Declare following on top
(What todo .. sometimes smal problems have such lenghty probelms... But this worked 10/10 for me)
Private Const PROCESS_ALL_ACCESS = &H1F0FFF
Private Declare Function GetWindowText Lib "user32" _
Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As _
String, ByVal cch As Long) As Long
Private Declare Function GetWindowTextLength Lib "user32" _
Alias "GetWindowTextLengthA" (ByVal hwnd As Long) As Long
Private Declare Function GetNextWindow Lib "user32" _
Alias "GetWindow" (ByVal hwnd As Long, ByVal wFlag As Long) _
As Long
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function GetWindowThreadProcessId Lib "user32" _
(ByVal hwnd As Long, lpdwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, lpExitCode As Long) As Long
Private Declare Function TerminateProcess Lib "kernel32" _
(ByVal hProcess As Long, ByVal uExitCode As Long) As Long
be happy
-Dany
Declare following on top
(What todo .. sometimes smal problems have such lenghty probelms... But this worked 10/10 for me)
Private Const PROCESS_ALL_ACCESS = &H1F0FFF
Private Declare Function GetWindowText Lib "user32" _
Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As _
String, ByVal cch As Long) As Long
Private Declare Function GetWindowTextLength Lib "user32" _
Alias "GetWindowTextLengthA" (ByVal hwnd As Long) As Long
Private Declare Function GetNextWindow Lib "user32" _
Alias "GetWindow" (ByVal hwnd As Long, ByVal wFlag As Long) _
As Long
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function GetWindowThreadProcessId Lib "user32" _
(ByVal hwnd As Long, lpdwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, lpExitCode As Long) As Long
Private Declare Function TerminateProcess Lib "kernel32" _
(ByVal hProcess As Long, ByVal uExitCode As Long) As Long
be happy
-Dany
ASKER
I don't understand why the way the objects are set or qualified make a difference in this situation, but it did. It seems like that if quit the application process all it should stop that process along with the objects assigned to it.
I don't claim to fully understand it but it seems that Excel treats any use of an unqualified 'global' object or method as implicitly creating a reference to a new Application variable. It would probably therefore be equally valid to use xl.Selection instead.
HTH
Rory
HTH
Rory
another thing, dont think it will make a difference but what if u moved set sh = nothing elsewhere
e.g.
wb.Save
wb.Close
xl.Quit
Set sh = Nothing
Set wb = Nothing
Set xl = Nothing