Closing Excel left open by another process

PowerShell Help
1

by mitchellb1 at 2012-12-20 12:06:11

Hi
We have a Powershell script that occasionally will leave Excel spreadsheets open, the spreadsheets are read and updated by the script and are not visible. Another script needs to use these spreadsheets and I need to test for their existence and close them. So far I’ve tried many things and this is the closest I’ve come.

$excelApp = [Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")
if ($excelApp.visible -eq $False)
{
$excelApp.visible = $True
$excelApp.quit()
[Runtime.InteropServices.Marshal]::ReleaseComObject($excelApp)
…

But the spreadsheet is not closing. I’ve tried spps -n excel but that closes all Excels, not just the non-visible ones. I could use spps -id if I knew the PID…

Any help would be much appreciated!

Bruce
by DonJ at 2012-12-20 13:54:53
Thus are the joys of working with Office COM objects through .NET Interop. It ain’t perfect, which is one reason I refuse to mess with Excel scripts myself!

COM objects don’t get a process ID per se; they run in-process. So if PowerShell isn’t properly releasing the COM object, closing the PowerShell process is the only thing that would do so.
by nohandle at 2012-12-21 02:18:43
[quote="mitchellb1"]We have a Powershell script that occasionally will leave Excel spreadsheets open[/quote]
Maybe figuring out why is this happening is better way to cure it :slight_smile:
If you keep track of the documents you opened you should be able to check if you closed them. Make the excel visible to investigate the issue. Then if you run it in hidden mode make sure it is set to be non interactive and not showing warnings (there are properties on the excel object to do this). That way you have full control over what the app is doing. On the excel object level (excel > workbook > worksheet) you have to make sure no opened document is left behind. Use try finally to make sure you do as much as you can to prevent leaking resources.
by mitchellb1 at 2012-12-21 08:29:44
Thanks for the comments, and yes, the best thing to do is address why it is happening. I will do that.

regards,
Bruce
by ArtB0514 at 2012-12-21 10:35:25
Excel often seems to hang when you are using automation, but in fact is waiting for user input from an invisible alert window. The fix for this is to use this before deleting, closing, or exiting:

$excelApp.DisplayAlerts = $false