How to Open; SaveAs; then Close an Excel 2013 (macro-enabled) workbook from PowerShell4

Question

Doing a search on the above Com operations yields links dating to '09 and even earlier. Perhaps it hasn't changed but I find myself bumping up against errors where 'it is being used by another process.' - even though no Excel app is open on my desktop. I have to reboot to resume.

To be clear - I'm trying to open an existing file; immediately SaveAs() (that much works), add a sheet, Save(); Close() - and then, importantly, repeat that cycle. In effect, I'm creating a few dozen new sheets within a loop that executes the above 'Open Master; SaveAs(); Edit Stuff; Save; Close;

From the examples I've seen this is not a typical workflow for PowerShell. Pasted at the very bottom is my provisional script - pretty rough and incomplete but things are opening what they need to open and adding sheet also works - until I know I have the right way to cleanly close stuff out I'm not worried about the iterations.

I've found a couple examples that address closing:

From http://theolddogscriptingblog.wordpress.com/2012/06/07/get-rid-of-the-excel-com-object-once-and-for-all/

$x = New-Object -com Excel.Application
$x.Visible = $True
Start-Sleep 5
$x.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($x)
Remove-Variable x

And from http://social.technet.microsoft.com/Forums/windowsserver/en-US/24e57b61-e792-40c1-8aff-b0a8205f48ab/updated-opened-excel-using-powershell?forum=winserverpowershell

Set-ItemProperty $path -name IsReadOnly -value $false
$Excel.ActiveWorkBook.Save()
$openfile.Close() 
$openfile = $null 
$Excel.Quit() 
$Excel = $null 
[GC]::Collect() 

<>

function MakeNewBook($theWeek, $AffID){
    $ExcelFile = "C:\csv\InvoiceTemplate.xlsm"
    $Excel = New-Object -Com Excel.Application
    $Excel.Visible = $True  
    $Workbook = $Excel.Workbooks.Open($ExcelFile)
    $theWeek = $theWeek  -replace "C:\\csv\\", ""
    $theWeek = $theWeek  -replace "\.csv", ""

    $theWeek = "c:\csv\Invoices\" +$AffID +"_" + $theWeek + ".xlsm"

    $SummaryWorksheet = $Workbook.worksheets.Item(1)

    $Workbook.SaveAs($theWeek)
    return $Excel
}

function MakeNewSheet($myBook, $ClassID){
    $SheetName = "w"+$ClassID
    #$Excel = New-Object -Com Excel.Application
    #$Excel.Visible = $True  
    $wSheet = $myBook.WorkSheets.Add()

}

function SaveSheet ($myExcel)
{
    #$WorkBook.EntireColumn.AutoFit()
    #Set-ItemProperty $path -name IsReadOnly -value $false
    $myExcel.ActiveWorkBook.Save()

    $openfile= $myExcel.ActiveWorkBook
    $openfile.Close() 
    $openfile = $null 
    $myExcel.Quit() 
    $myExcel = $null 
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($myExcel)
    Remove-Variable $myExcel
    [GC]::Collect() 

}

$theWeek = "C:\csv\wkStart2013-11-04.csv"
$x = Import-Csv $theWeek

foreach ($xLine in $x){
    if ($x[0]){
        $AffID = $x[1].idAffiliate
        $myExcel = MakeNewBook $theWeek  $x[1].idAffiliate

        $ClassID = $x[1].idClass
        MakeNewSheet $myExcel $ClassID
        continue
    }
    SaveSheet($myExcel)
    $AffID = $_.$AffID
    $wID = $xLine.idClass
    #MakeNewSheet($wID)
    Echo "$wID"

}

Solution 1

My experience has been that the Quit method doesn't work well, especially when looping. When you get the error, instead of rebooting, open up Task Manager and look at the Processes tab. I'm willing to bet you'll see Excel still open -- maybe even multiple instances of it. I solved this problem by using Get-Process to find all instances of Excel and piping them to Stop-Process. Doesn't seem like that should be necessary, but it did the trick for me.

Solution 2

As a follow up after playing around with this issue myself. I geared my solution around Ron Thompson's comment minus the function calls:

# collect excel process ids before and after new excel background process is started
$priorExcelProcesses = Get-Process -name "*Excel*" | % { $_.Id }
$Excel = New-Object -ComObject Excel.Application
$postExcelProcesses = Get-Process -name "*Excel*" | % { $_.Id }

# your code here (probably looping through the Excel document in some way

# try to gently quit
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)

# otherwise allow stop-process to clean up
$postExcelProcesses | ? { $priorExcelProcesses -eq $null -or $priorExcelProcesses -notcontains $_ } | % { Stop-Process -Id $_ }

Solution 3

You should not have to keep track of processes and kill them off.

My experience has been that to properly and completely close Excel (including in loops), you also need to release COM references. In my own testing have found removing the variable for Excel also ensures no remaining references exist which will keep Excel.exe open (like if you are debugging in the ISE).

Without performing the above, if you look in Task Manager, you may see Excel still running...in some cases, many copies.

This has to do with how the COM object is wrapped in a “runtime callable wrapper".

Here is the skeleton code that should be used:

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$workbook = $excel.Workbooks.Add()
# or $workbook = $excel.Workbooks.Open($xlsxPath)

# do work with Excel...

$workbook.SaveAs($xlsxPath)
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
# no $ needed on variable name in Remove-Variable call
Remove-Variable excel

Comments

[Rana Hamza]

I was all prepared to tell you that i'd already checked running processes - but running via the command line showed Excel processes not shown via the Task Manager. Thanks!

[user4317867]

Using Stop-Process -Name EXCEL on a server where other users have Excel open would cause PowerShell to terminate those instances of Excel. Instead, use While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject(‌​$Excel)) {'cleanup Excel'} and for good measure [gc]::collect() | Out-Null;[gc]::WaitForPendingFinalizers() | Out-Null

[Ron Thompson]

The way that I resolve this is to collect all previous running instances of Excel with function startExcel() { $beforeExcel = @(); $afterExcel = @(); Get-Process -name "*Excel*" | %{$beforeExcel += $_.Id}; $excel = New-Object -ComObject Excel.Application; Get-Process -name "*Excel*" | %{$afterExcel += $_.Id}; } and function murderExcel() {foreach($id in $afterExcel) { if($beforeExcel -notcontains $id) { Stop-Process -Id $id} } }

[Ron Thompson]

As long as the above commented code doesn't have an instance of Excel started in the milliseconds between collecting the PIDs, it's normally safe to murder the rest of them. I've not had it fail me yet. /knocksonwood.

[Robert McMahan]

Sledge hammer approach: taskkill /F /IM EXCEL.EXE But, I'm a fan of Ron Thompson's approach. It's much cleaner and doesn't kill excel processes that were open prior to running your script.

[AllanT]

When writing code in an answers it is helpful to use the code formatting option.