?
Solved

Powershell - Excell automation process still runs

Posted on 2010-08-26
5
Medium Priority
?
936 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 71

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 71

Accepted Solution

by:
Chris Dent earned 1000 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 71

Expert Comment

by:Chris Dent
ID: 33535092

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

Chris
0

Featured Post

Ransomware Attacks Keeping You Up at Night?

Will your organization be ransomware's next victim?  The good news is that these attacks are predicable and therefore preventable. Learn more about how you can  stop a ransomware attacks before encryption takes place with our Ransomware Prevention Kit!

Question has a verified solution.

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

The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

777 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