Excel remains running

This topic contains 3 replies, has 2 voices, and was last updated by Profile photo of Steve Larson Steve Larson 3 years, 9 months ago.

  • Author
    Posts
  • #8305
    Profile photo of marleen denert
    marleen denert
    Participant

    < !–[if gte mso 9]>



    < ![endif]–>

    < !–[if gte mso 9]>

    Normal
    0


    21


    false
    false
    false

    NL-BE
    X-NONE
    X-NONE
























    < ![endif]–>< !–[if gte mso 9]>











































































































































    < ![endif]–>< !–[if gte mso 10]>

    < ![endif]–>

    The script below works fine, but  Excel is not really stopped running, unless I apply the method Release-Ref on almost every reference-object (see code)

    If I remove # for all these method-calls, Excel is stopped when the script finishes.

    But I’m looking for a solution that is less complicated, because it is not ‘nice’ to look for all the reference-objects in your code.

     Who can help me to find a simple solution.

    function Release-Ref ($ref) {

       while([System.Runtime.InteropServices.Marshal]::ReleaseComObject($ref) )

       {}

    }

    $excelAppl=new-object -comObject "Excel.Application"

    $excelAppl.DisplayAlerts = $false

     

    #openen bestaand bestand

    $book=$excelAppl.Workbooks.Open("c:\users\marleen\documents\test.xlsx")

    $ws=$book.Worksheets;

    foreach ($sheet in $ws)

    {

         $range=$sheet.UsedRange;

         Write-Host $range.columns.count " "$range.rows.count;

         #Release-Ref $range;

        

    }

    #Release-Ref $sheet

    #Release-Ref $ws;

     

    $book.Close()

    #Release-Ref $book;

    Start-Sleep 1

     

    $excelAppl.Quit()

     

    Start-Sleep 1

    Release-Ref $excelAppl;

    [System.GC]::Collect()

    [System.GC]::WaitForPendingFinalizers()

     

     

  • #8310
    Profile photo of marleen denert
    marleen denert
    Participant

    < !–[if gte mso 9]>



    < ![endif]–>

    < !–[if gte mso 9]>

    Normal
    0


    21


    false
    false
    false

    NL-BE
    X-NONE
    X-NONE
























    < ![endif]–>< !–[if gte mso 9]>











































































































































    < ![endif]–>< !–[if gte mso 10]>

    < ![endif]–>

     

    The script below works fine, but  Excel is not really stopped running, unless I apply the method Release-Ref on almost every reference-object.

     

    If I remove # for all these method-calls, Excel is stopped when the script finishes.

     

    But I’m looking for a solution that is less complicated, because it is not ‘nice’ to look for all the reference-objects in your code.

    Who can help me ?

  • #8785
    Profile photo of Steve Larson
    Steve Larson
    Participant

    Marleen,
    This is a code segment that shows what I use for the Excel not closing problem. It happens I am just automating saving the .XLSX file as .CSV. What you would want is the first line getting the list of processes at the start of Excel automation and the last line after the Excel automation that kill all Excel processes that are still running that were not in the first list. It assumes no Excel process starts in the meantime that you care about–that is a safe assumption in my case.
    $runningExcelProcesses = @(Get-Process -Name excel -ErrorAction SilentlyContinue | Select-Object ID)
    $runningExcelProcesses
    $xls="T:somefilepath.xlsx"
    $csv="T:someotherfilepath.csv"
    # You can use the Excel COM object in PowerShell to save the file as a CSV file. http://www.powershellcommunity.org/Forums/tabid/54/aft/657/Default.aspx
    # $xlCSV is a constant for the SaveAs method that tells Excel to save the file in the CSV format.
    # xlWorkbookDefault = 51 http://www.excel-answers.com/microsoft/Excel-Programming/34263208/fileformat-list.aspx and http://www.pcreview.co.uk/forums/use-automation-save-xls-into-pdf-office-2007-a-t3042211.html
    new-variable xlCSV 6 -option Constant -ErrorAction SilentlyContinue # $xlCSV=6
    new-variable xlWorkbookDefault 51 -option Constant -ErrorAction SilentlyContinue # $xlWorkbookDefault=51
    $xl=New-Object -com "Excel.Application"
    $wb=$xl.workbooks.open($xls)
    $wb.SaveAs($csv,$xlCSV) # create a CSV version of the file
    $xl.displayalerts=$False
    $xl.quit()
    # One thing that seems to be a problem though is that even though you quit Excel, the Excel process doesn't really terminate so you may also need kill it
    # if (ps excel) { kill -name excel}
    Get-Process -Name excel -ErrorAction SilentlyContinue | Select-Object ID | Where-Object {$runningExcelProcesses.ID -notcontains $_.ID} | Stop-Process

    Steve

  • #8798
    Profile photo of Steve Larson
    Steve Larson
    Participant

    Marleen,
    I have included a code segment below that shows how I handle the problem with Excel not closing. What you would be interested in is the first line that get a list of existing Excel processes before the Excel automation starts and the last line that closes any new Excel processes since the snapshot. The segment just opens Excel and saves the file as a .CSV. It assumes that there are no Excel processes started that you would care about in the meantime–a safe assumption in my case.

    $runningExcelProcesses = @(Get-Process -Name excel -ErrorAction SilentlyContinue | Select-Object ID)
    $runningExcelProcesses
    $xls="T:somefilepath.xlsx"
    $csv="T:someotherfilepath.csv"
    # You can use the Excel COM object in PowerShell to save the file as a CSV file. http://www.powershellcommunity.org/Forums/tabid/54/aft/657/Default.aspx
    # $xlCSV is a constant for the SaveAs method that tells Excel to save the file in the CSV format.
    # xlWorkbookDefault = 51 http://www.excel-answers.com/microsoft/Excel-Programming/34263208/fileformat-list.aspx and http://www.pcreview.co.uk/forums/use-automation-save-xls-into-pdf-office-2007-a-t3042211.html
    new-variable xlCSV 6 -option Constant -ErrorAction SilentlyContinue # $xlCSV=6
    new-variable xlWorkbookDefault 51 -option Constant -ErrorAction SilentlyContinue # $xlWorkbookDefault=51
    $xl=New-Object -com "Excel.Application"
    $wb=$xl.workbooks.open($xls)
    $wb.SaveAs($csv,$xlCSV) # create a CSV version of the file
    $xl.displayalerts=$False
    $xl.quit()
    # One thing that seems to be a problem though is that even though you quit Excel, the Excel process doesn't really terminate so you may also need kill it
    # if (ps excel) { kill -name excel}
    Get-Process -Name excel -ErrorAction SilentlyContinue | Select-Object ID | Where-Object {$runningExcelProcesses.ID -notcontains $_.ID} | Stop-Process

    Steve

You must be logged in to reply to this topic.