[vba] really quitting excel

[timv]

The problem is that from my visual basic program my excel application won't quit. It stays in the windows task manager even after xlApp.Quit() and xlApp=Nothing.
My program runs every day, so after 2 weeks I have 14 excel processes in the task manager and that's not what i want.

I read http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/ but didn't really get a solution.

my code (it opens excel files and updates their pivot tables):

Module Module1

    Sub Main()
        ' Get command line arguments
        Dim CommandString() As String
        CommandString = Split(Command, ",")

        ' Start Excel
        Dim xlapp As Object 'Excel.Application
        xlapp = CreateObject("Excel.Application")

        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")

        ' Refresh all workbooks
        Dim counter As Long
        Dim strFileName As String

        For counter = 0 To UBound(CommandString)
            Try
                strFileName = CommandString(counter)

                ' Get the workbook
                Dim xlbook As Object 'Excel.Workbook
                ' Some files are protected with a password, others aren't

                xlbook = xlapp.Workbooks.Open(strFileName, password:="test")
                ' Add a module (to which the macro will be added) to the workbook 
                Dim xlmodule As Object 'VBComponent
                xlmodule = xlbook.VBProject.VBComponents.Add(1)     'vbext_ct_StdModule

                ' Create the macro code
                Dim strCode As String
                strCode = _
                "sub RefreshPivotTable()" & vbCr & _
                    "   Sheets(1).PivotTables(1).RefreshTable" & vbCr & _
                "end sub"

                ' Insert the macro code into the module
                xlmodule.CodeModule.InsertLines(1, strCode)

                ' Let excel not display its alerts for saving
                xlapp.DisplayAlerts = False
                ' Run the new macro!
                xlapp.Run("RefreshPivotTable")

                ' Remove (from the workbook) and release module
                xlbook.VBProject.VBComponents.Remove(xlmodule)
                xlmodule = Nothing

                xlbook.Close(SaveChanges:=True)
                xlapp.DisplayAlerts = True
                xlbook = Nothing
            Catch Exc As System.Runtime.InteropServices.COMException
                'Continue For 'caught a user entering a wrong password for this excel workbook
            End Try
        Next

        ' Quit Excel
        xlapp.Quit()
        xlapp = Nothing
    End Sub

End Module

Does someone has a solution for what to change in my code that could solve this problem?

[DeepButi]

timv,
this is one of the most read threads in this forum. Take a look here .

Hope it helps

[timv]

thanks for pointing me, deepbuti,

i'v tried alot and the strange thing is, when i test my application on my pc in debug or release mode: everything goes well, after setting xlApp to Nothing the EXCEL.EXE disappears from my taskmanager.
But when using the (release version) application on the server where it's supposed to be, EXCEL.EXE doesn't disappear.

Both computers use the same .net framework and windows xp professional sp1.
This is strange if you ask me.

[Gizmo001]

The problem may be, though it should not be, that when an exception occurs (because of wrong password, may be), the workbook that is created within the for..next loop is still open (it gets closed only when no exceptions are genrated). Within the catch.. end try segment, close the workbook.

Technically, as workbook is connected to the application, it should close when the application closes.

Let me know if this helps.

[timv]

i think that would be very strange, because since the open method throws the exception, the workbook isn't opened yet.
Also, my excel files are always all updated (those with and without password set), so i guess the catch() is never executed. And when i start the application with the same parameters on my pc, it does work.


what's that i read about the references. NET and COM-references? I read

This dialog box edits the project references file for the selected project. A project references file contains information about the run-time requirements of an application or component, such as which files are needed, how they are to be registered, and where on the user's machine they should be installed.

Is this necessary? And what references?

greets

[Gizmo001]

Is it a network server on which you are loading this or an Internet Server? It is recommended that you don't use Excel on an Internet server for a multitude of reasons (Microsoft website addresses the issues). Just a thought - I am as baffled as you are. Hope someone can diagnose this one.