对上述 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"
}