Solved

Powershell - Excell automation process still runs

Posted on 2010-08-26
5
817 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Are you one of those front-line IT Service Desk staff fielding calls, replying to emails, all-the-while working to resolve end-user technological nightmares? I am! That's why I have put together this brief overview of tools and techniques I use in o…
This script checks a path to see if a folder exists. If the folder does exist you will get output "The folder has previously been created. No action taken" If not it will create the folder. Then adds one user modify permission to the folder. It …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

746 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

12 Experts available now in Live!

Get 1:1 Help Now