如何打开；另存为;然后从PowerShell4关闭Excel 2013(启用宏)工作簿 [英] How to Open; SaveAs; then Close an Excel 2013 (macro-enabled) workbook from PowerShell4

问题描述

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

Doing a search on the above Com operations yields links dating to '09 and even earlier. Perhaps it hasn't changed but I find myself bumping up against errors where 'it is being used by another process.' - even though no Excel app is open on my desktop. I have to reboot to resume.

要明确-我正在尝试打开现有文件；立即SaveAs()(可以正常工作)，添加工作表Save(); Close()-然后，重要的是，重复该循环.实际上，我正在执行上述"Open Master"的循环内创建了几十个新工作表. SaveAs();编辑资料；救;关闭；

To be clear - I'm trying to open an existing file; immediately SaveAs() (that much works), add a sheet, Save(); Close() - and then, importantly, repeat that cycle. In effect, I'm creating a few dozen new sheets within a loop that executes the above 'Open Master; SaveAs(); Edit Stuff; Save; Close;

从示例中我已经看到，这不是PowerShell的典型工作流程.在我的临时脚本的最底部粘贴-非常粗糙和不完整，但是事情正在打开它们需要打开的内容并添加工作表也可以正常工作-直到我知道我有正确的方法将所有内容清理干净为止，我不担心迭代

From the examples I've seen this is not a typical workflow for PowerShell. Pasted at the very bottom is my provisional script - pretty rough and incomplete but things are opening what they need to open and adding sheet also works - until I know I have the right way to cleanly close stuff out I'm not worried about the iterations.

我找到了一些解决结案的例子:

I've found a couple examples that address closing:

<>

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"

}

推荐答案

我的经验是Quit方法不能很好地工作，尤其是在循环时.收到错误消息后，无需重新启动，而是打开任务管理器"并查看进程"选项卡.我敢打赌，您会看到Excel仍处于打开状态-甚至可能有多个实例.我通过使用Get-Process查找所有Excel实例并将它们传递给Stop-Process来解决了这个问题.看来这不是必需的，但这对我来说是成功的秘诀.

My experience has been that the Quit method doesn't work well, especially when looping. When you get the error, instead of rebooting, open up Task Manager and look at the Processes tab. I'm willing to bet you'll see Excel still open -- maybe even multiple instances of it. I solved this problem by using Get-Process to find all instances of Excel and piping them to Stop-Process. Doesn't seem like that should be necessary, but it did the trick for me.

这篇关于如何打开；另存为;然后从PowerShell4关闭Excel 2013(启用宏)工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！