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?

Answer 1

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()