Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1052
  • Last Modified:

Powershell - Excell automation process still runs

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
chad
Asked:
chad
  • 3
  • 2
1 Solution
 
Chris DentPowerShell DeveloperCommented:

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
 
chadAuthor Commented:
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
 
Chris DentPowerShell DeveloperCommented:

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
 
chadAuthor Commented:
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
 
Chris DentPowerShell DeveloperCommented:

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

Chris
0

Featured Post

Evaluating UTMs? Here's what you need to know!

Evaluating a UTM appliance and vendor can prove to be an overwhelming exercise.  How can you make sure that you're getting the security that your organization needs without breaking the bank? Check out our UTM Buyer's Guide for more information on what you should be looking for!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now