Solved

Powershell - Excell automation process still runs

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Set OWA language and time zone in Exchange for individuals, all users or per database.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 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