Problem with Excel (Not visible and not closing).

[TheBugSlayer]

I have a procedure that opens a text file in Excel and does some formatting. However the Excel application is not visible but I see it as a process in Task Manager and, even when a close it and free the objects it still remains in there, preventing me from running the program again if I don't close it manually.

Can anybody help? See code below.

[b]
Private Sub PrepData(CCtr As String, DataFileName As String)
Dim XLApp As New Excel.Application
    
    Application.DisplayAlerts = False
        
    With XLApp
[COLOR=red]
        'WindowState = xlNormal
        Visible = True
[/color]
        Workbooks.OpenText DataFileName, _
            Origin:=xlWindows, StartRow:=6, DataType:=xlFixedWidth, FieldInfo:= _
            Array(Array(0, 1), Array(1, 2), Array(10, 3), Array(18, 2), Array(74, 2), Array(90, 2), _
            Array(106, 9), Array(130, 2))
    
        Range("B1").Select
        Selection.Cut
        Range("C1").Select
        ActiveSheet.Paste
   
        Dim y As Integer

        y = 1

        Dim Current As Excel.Range
    
        While Cells.Item(y, 1) <> &quot;&quot;

            Set Current = Cells.Item(y, 1)
    
            If IsDate(Cells.Item(y, 3)) Then
            Cells.Item(y, 11).Value = CCtr
            Cells.Item(y, 12).Value = Cells.Item(1, 3).Value
            Else
                Cells.Item(y, 3).EntireRow.Delete
                y = y - 1
            End If
    
            y = y + 1
    
        Wend

        Rows(&quot;1:1&quot;).Select
        Selection.Delete Shift:=xlUp
        y = 2

        While Cells.Item(y, 1) <> &quot;&quot;
    
            Range(Cells(y, 3), Cells(y, 4)).Select
    
            Selection.Copy
            ActiveSheet.Paste Destination:=Cells(y - 1, 8)
                  
            Cells(y, 3).EntireRow.Delete
    
            y = y + 1

        Wend

        Columns(&quot;I:I&quot;).Select
        Selection.TextToColumns Destination:=Range(&quot;I1&quot;), DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 2), Array(33, 2))
        Columns(&quot;E:H&quot;).Select
        Selection.Insert Shift:=xlToRight
        Columns(&quot;D:D&quot;).Select
        Selection.TextToColumns Destination:=Range(&quot;D1&quot;), DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 2), Array(17, 2), Array(20, 2), Array(23, 2), Array(41, 2))
        Columns(&quot;A:A&quot;).Select
        Selection.Delete Shift:=xlToLeft
        Columns(&quot;L:L&quot;).Select
        Selection.Replace What:=&quot;EFT&quot;, Replacement:=&quot;&quot;, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False
        Cells.Select
        Cells.EntireColumn.AutoFit
        Range(&quot;A1:O1&quot;).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
    End With
    AppActivate &quot;notepad&quot;
    SendKeys &quot;^V&quot;
[COLOR=red]
    XLApp.Quit
    Set XLApp = Nothing
[/color]
    Application.DisplayAlerts = True
End Sub
[/b]

 

[snoopdougie]

Hi there,

Try closing the workbook first.

I take it you don't need to save the data in excel, so you can close it with ActiveWorkbook.Close(false,DataFileName)

XLApp.Quit usually ends the process even if the workbook is open, but since this is not happening here, it might work to close the workbook first.

 

[snoopdougie]

Sorry, closing the wb doesn't help, I checked your code on my side and understand your problem.

The EXCEL process should end when the application from which you are running the code ends.

I have had the same problem with similar code in ASP.NET, but in ASP.NET, even closing the app doesn't kill the process. I am very interested in any solutions that come up.

Does it matter if you dont kill the XL object? If you close the workbook each time, but keep the XL object open and reuse it each time you call PrepData, you will only have a max of one XL process open, which should close when you finally end your app.

 

[gavinhuet]

Hi,

Your problem is this statement:

Dim XLApp As New Excel.Application

Because you have chosen to auto-instantiate the excel object, every call to it will reinstantiate the object, therefore the lines

xlApp.Quit
Set xlApp = Nothing

will not work.

What you need to do is instantiate and use Excel as follows:

Dim xlApp As Excel.Application

  Set xlApp = New Excel.Application

  . . .

  xlApp.Quit
  Set xlApp = Nothing

Regards
-- Gavin

 

[TheBugSlayer]

Thank you guys for your input.

Gavin, your solution works partially. If I run the command only once and close the application, the process also closes. If I run it more than once, it adds a process each time and that one only is terminated when execution completes; so when I close the application there is always one process that remains there.

The other thing is, why is Excel not showing? Also statement

XLApp.WindowState = xlNormal

is giving an error, why?