如何打开；另存为;然后从 PowerShell4 关闭 Excel 2013（启用宏）工作簿

对上述 Com 操作进行搜索会产生 09 年甚至更早的链接。也许它没有改变，但我发现自己遇到了“它正在被另一个进程使用”的错误。 - 即使我的桌面上没有打开 Excel 应用程序。我必须重新启动才能恢复。

需要明确的是 - 我正在尝试打开一个现有文件；立即地SaveAs()（就这么多），添加一张纸，Save(); Close()- 然后，重要的是，重复这个循环。实际上，我在执行上述“Open Master;”的循环中创建了几十个新工作表。SaveAs();编辑内容；节省;关闭;

从我看到的示例中，这不是 PowerShell 的典型工作流程。粘贴在最底部的是我的临时脚本 - 相当粗糙和不完整，但事情正在打开他们需要打开的内容，并且添加工作表也有效 - 直到我知道我有正确的方法来干净地关闭东西我不担心迭代。

我发现了几个解决关闭问题的例子：

来自http://theolddogscriptingblog.wordpress.com/2012/06/07/get-rid-of-the-excel-com-object-once-and-for-all/

$x = New-Object -com Excel.Application
$x.Visible = $True
Start-Sleep 5
$x.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($x)
Remove-Variable x

并来自http://social.technet.microsoft.com/Forums/windowsserver/en-US/24e57b61-e792-40c1-8aff-b0a8205f48ab/updated-opened-excel-using-powershell?forum=winserverpowershell

Set-ItemProperty $path -name IsReadOnly -value $false
$Excel.ActiveWorkBook.Save()
$openfile.Close() 
$openfile = $null 
$Excel.Quit() 
$Excel = $null 
[GC]::Collect() 

<>

function MakeNewBook($theWeek, $AffID){
    $ExcelFile = "C:\csv\InvoiceTemplate.xlsm"
    $Excel = New-Object -Com Excel.Application
    $Excel.Visible = $True  
    $Workbook = $Excel.Workbooks.Open($ExcelFile)
    $theWeek = $theWeek  -replace "C:\\csv\\", ""
    $theWeek = $theWeek  -replace "\.csv", ""

    $theWeek = "c:\csv\Invoices\" +$AffID +"_" + $theWeek + ".xlsm"

    $SummaryWorksheet = $Workbook.worksheets.Item(1)

    $Workbook.SaveAs($theWeek)
    return $Excel
}

function MakeNewSheet($myBook, $ClassID){
    $SheetName = "w"+$ClassID
    #$Excel = New-Object -Com Excel.Application
    #$Excel.Visible = $True  
    $wSheet = $myBook.WorkSheets.Add()

}

function SaveSheet ($myExcel)
{
    #$WorkBook.EntireColumn.AutoFit()
    #Set-ItemProperty $path -name IsReadOnly -value $false
    $myExcel.ActiveWorkBook.Save()

    $openfile= $myExcel.ActiveWorkBook
    $openfile.Close() 
    $openfile = $null 
    $myExcel.Quit() 
    $myExcel = $null 
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($myExcel)
    Remove-Variable $myExcel
    [GC]::Collect() 

}

$theWeek = "C:\csv\wkStart2013-11-04.csv"
$x = Import-Csv $theWeek

foreach ($xLine in $x){
    if ($x[0]){
        $AffID = $x[1].idAffiliate
        $myExcel = MakeNewBook $theWeek  $x[1].idAffiliate

        $ClassID = $x[1].idClass
        MakeNewSheet $myExcel $ClassID
        continue
    }
    SaveSheet($myExcel)
    $AffID = $_.$AffID
    $wID = $xLine.idClass
    #MakeNewSheet($wID)
    Echo "$wID"

}

作为我自己解决这个问题后的后续行动。我围绕罗恩·汤普森的评论调整了我的解决方案，减去了函数调用：

# collect excel process ids before and after new excel background process is started
$priorExcelProcesses = Get-Process -name "*Excel*" | % { $_.Id }
$Excel = New-Object -ComObject Excel.Application
$postExcelProcesses = Get-Process -name "*Excel*" | % { $_.Id }

# your code here (probably looping through the Excel document in some way

# try to gently quit
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)

# otherwise allow stop-process to clean up
$postExcelProcesses | ? { $priorExcelProcesses -eq $null -or $priorExcelProcesses -notcontains $_ } | % { Stop-Process -Id $_ }