[powershell]Qutting com-object

I was trying to test a powershell script to read information from an excel file, I searched through the internet and found that using com object to open an excel workbook, however the close workbook and quit excel does not actually work, the excel application is still in the processes. I search through the internet and found a perfect answer to the problem, see the forum here: https://social.technet.microsoft.com/Forums/lync/en-US/81dcbbd7-f6cc-47ec-8537-db23e5ae5e2f/excel-releasecomobject-doesnt-work?forum=ITCG The solution provided by Ryan Kolter works. I used his solution and my excel closed instantaneously after the script completed.

#ignore certificate validation
add-type @"
    using System.Net;
    using System.Security.Cryptography.X509Certificates;
    public class TrustAllCertsPolicy : ICertificatePolicy {
        public bool CheckValidationResult(
            ServicePoint srvPoint, X509Certificate certificate,
            WebRequest request, int certificateProblem) {
            return true;
        }
    }
"@
[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy
$a = 2
$src = @() # source ip address collection
$tests = @() # source hostnames collection
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $false # To prevent the excel from opening visibly.
$wb = $xl.Workbooks.Open("H:\Tufin\tests\Rulebook.xlsx")


#uncomment to check the sheet in a workbook.
#$wb.Sheets | Select-Object -Property Name

$ws = $wb.Sheets.item("Rule")

#$aa = "A" + $a
while($true)
{
    if($ws.Range("A" + $a).Text -ne "") # collect until an empty cell.
    {
        $tests += $ws.Range("A" + $a).Text
    }
    else
    {
        break;
    }
    $a += 1
}
foreach($test in $tests) {
    $src += [System.Net.DNS]::GetHostEntry($test).AddressList.IPAddressToString
}


#Clean up
$xl.Workbooks.Close()
$xl.Quit()
[void][System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($ws)
[void][System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($wb)
[void][System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($xl)
[GC]::Collect()
Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s