Solved

Powershell - Excell automation process still runs

Posted on 2010-08-26
5
824 Views
Last Modified: 2012-05-10
I have a script that will open an existing xlsm document and save it back once things are complete.  The script works well except that when the file closes an excel.exe process still runs in the background.  If I run the script a few times I will have 1 excel.exe process for each.  Is there something that I can add to the script to eliminate or prevent the continued process from existing?

I do not want a brute force method though.

thanks in advanced.
C
$a = New-Object -comobject Excel.Application 
    $a.visible = $true
    $path =  "C:\powershell\source\"
    $file1 = get-content "c:\powershell\source\sourcefile.txt"
    $pathFile = $Path + $file1
    $b = $a.Workbooks.Open($pathfile)
    $c = $b.Worksheets.Item(7)






$fs = New-Object -ComObject scripting.filesystemobject
    $absolutepath = $fs.GetAbsolutePathName("c:\powershell\Results\" + $file1)
    $a.application.displayalerts = $false
    $b.saveas($absolutepath)
    $a.quit()

Open in new window

0
Comment
Question by:kabaam
  • 3
  • 2
5 Comments
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33533378

You have to release the COMObject and kill of the variable:

[Runtime.Interopservices.Marshal]::ReleaseComObject($a)
Remove-Variable a

Which "should" work. Excel is a bit of a pain.

Chris
0
 
LVL 11

Author Comment

by:kabaam
ID: 33534612
I like that "Should" work.  In fact it did not.  BUT almost.
in my code I have to releasecomobject for any variable I associated with the xls file.

which includes:
a
b
c
and for some reason I needed to release $a twice.  Any idea why that is?

So, this is the only way I can get it to work:

    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($c)
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($b)
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($a)
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($a)
   
    Remove-Variable a
    Remove-Variable b
    Remove-Variable c
0
 
LVL 70

Accepted Solution

by:
Chris Dent earned 250 total points
ID: 33534883

Finally found it, you might enjoy reading this:

http://technet.microsoft.com/en-us/library/ff730962.aspx

I found it when I faced the same problem several months ago. Start out at "Uh-oh." and you'll be in the middle of the technical explanation. The short version is that .NET (rather than just PS) doesn't shut the COMObject down properly.

Fun, isn't it? :)

Chris
0
 
LVL 11

Author Comment

by:kabaam
ID: 33535008
Lots of fun.  I was looking at that same technet link after your first post.  Gotta love it.
I feel like I am going to pester you all week with powershell questions.  I like learning new things.

C
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33535092

That's okay, PowerShell really is good fun :)

Chris
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
A brief introduction to what I consider to be the best editor for PowerShell.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now