How to properly close Excel.Application?

Question

I have a small script that allows me to merge multiple .csv to a .xlsx:

$path = "C:\Users\FrancescoM\Desktop\CSV\Results\*"
$csvs = Get-ChildItem $path -Include *.csv
$y = $csvs.Count
Write-Host "Detected the following CSV files: ($y)"
Write-Host " "$csvs.Name"`n"
$outputfilename = "Final Registry Results"
Write-Host Creating: $outputfilename
$excelapp = New-Object -ComObject Excel.Application
$excelapp.SheetsInNewWorkbook = $csvs.Count
$xlsx = $excelapp.Workbooks.Add()
for ($i=1; $i -le $y; $i++) {
    $worksheet = $xlsx.Worksheets.Item($i)
    $worksheet.Name = $csvs[$i-1].Name
    $file = (Import-Csv $csvs[$i-1].FullName)
    $file | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Clip
    $worksheet.Cells.Item(1).PasteSpecial() | Out-Null
}

$output = "Results.xlsx"
$xlsx.SaveAs($output)
$excelapp.Quit()

If I run it once it works perfectly and it creates my "Results.xlsx" file.

But if I then delete the "Results.xlsx" file and I run the code again I receive this error:

A file named 'Results.xlsx' already exists in this location. Do you want to replace it?

But it's evident that the file is not there any more. I believe I'm closing the Excel.Application the wrong way. How to close it right?

You defined `$output` without a path, so Excel creates the file in its current working directory. Please double check that this working directory is what you think it is (e.g. specify `$output` with the full path to the output file and see if the error persists). — Ansgar Wiechers, Nov 09 '18 at 09:48
On top of what Ansgar commented, to really close and free the Com objects, put this after the `$excelapp.Quit()` method: `[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsx) | Out-Null; [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelapp) | Out-Null; [System.GC]::Collect(); [System.GC]::WaitForPendingFinalizers()` — Theo, Nov 10 '18 at 14:19
@AnsgarWiechers, I used `PowerShell` in the title because I like to mention the main topic of the question. I taught this was good for Google indexing but as it's the second time you edit one of my post I will follow your suggestion and I will not use it again. Let me know if there is anything else I need to know and thank you — Francesco Mantovani, Nov 11 '18 at 10:18
@Theo, your code did the trick! If you post it as answer I will mark it as the right one. Thank you — Francesco Mantovani, Nov 11 '18 at 10:19
Was your problem fixed by releasing the COM object, or by specifying a full path to the output file. In case of the former your question is a duplicate of the one Kory Gill had referenced before. — Ansgar Wiechers, Nov 11 '18 at 23:18
Meta StackExchange has a [community FAQ](https://meta.stackexchange.com/questions/7931/faq-for-stack-exchange-sites). The consensus on tags in subjects is covered in [this Q/A](https://meta.stackexchange.com/questions/19190/should-questions-include-tags-in-their-titles) from there. — Ansgar Wiechers, Nov 11 '18 at 23:29

score 2 · Accepted Answer · answered Nov 11 '18 at 10:28

As Ansgar Wiechers commented, it is better to use a full path and filename for this piece of code $output = "Results.xlsx" otherwise, the output will be written to Excel's current directory and that may not be where you expect it.

To answer the question How to properly close Excel.Application?, you need to not only quit Excel when done, but also release the Com objects used in the code. You do this like so:

$excelapp.Quit()

# release the WorkSheet Com object
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsx) | Out-Null
# release the Excel.Application Com object
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelapp) | Out-Null 
# Force garbage collection
[System.GC]::Collect()
# Suspend the current thread until the thread that is processing the queue of finalizers has emptied that queue.
[System.GC]::WaitForPendingFinalizers()

Thanks for the snippet; I tried it but with TaskManager, I still see a process running for Microsoft Excel. — Nicolas Rouquette, Aug 18 '21 at 21:47
@NicolasRouquette That depends. Windows Garbage collection runs every once in a while. You have no control over that. When it runs, it cleans memory from all processes and memoryblocks that are released or have fallen out of scope. Sometimes this happens really quickly after the code has run on other occasions it can take a while. — Theo, Aug 19 '21 at 11:04

How to properly close Excel.Application?

1 Answers1