Solved

Instance of Excel still running as process in Task Manager

Posted on 2006-07-03
9
321 Views
Last Modified: 2013-12-03
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.Application")
Set wd = objWord.documents.Open(strNew)
objWord.Application.Visible = False
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.txt", Origin:=xlWindows, StartRow:=10 _
        , 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
0
Comment
Question by:Mach1pro
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17030635
Does it still fail even if u use late binding with Excel? U use it for word, so why not try for Excel as well

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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17030650
I think the other way to close excel is xl.Application.Quit
though I dont expect it makes much difference
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 17030665
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
0
 
LVL 6

Author Comment

by:Mach1pro
ID: 17031265
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.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 400 total points
ID: 17031399
I've just realised you are not qualifying your selection object, which is probably what is causing the problem. Try changing this:
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

to this:
with sh.Rows("1:1")
    .Insert Shift:=xlDown
    .Insert Shift:=xlDown
end With
with sh.Range("H8:J8")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = True
End With


and hopefully that should solve it.

Regards,
Rory
0
 

Expert Comment

by:deandany
ID: 17032846
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(hwnd, lProcessID)
'Get handle to process
hProcess = OpenProcess(PROCESS_ALL_ACCESS, 0&, lProcessID)
If hProcess <> 0 Then
    'get exit code
    GetExitCodeProcess hProcess, lExitCode
        If lExitCode <> 0 Then
                'bye-bye
            lRet = TerminateProcess(hProcess, lExitCode)
            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


0
 

Expert Comment

by:deandany
ID: 17032894
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
0
 
LVL 6

Author Comment

by:Mach1pro
ID: 17032982
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 17033972
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now