PowerShell,关闭Excel

r3i60tvu  于 2022-11-10  发布在  Shell
关注(0)|答案(3)|浏览(374)

我有以下代码,它工作得很好,只是它不能正确地关闭Excel。它使一个Excel进程处于运行状态。
有没有一种方法可以在不终止进程的情况下正确关闭Excel?
由于我在运行此脚本时正在使用其他Excel文件,因此无法终止所有活动的Excel进程。
我想我在网上找到的都试过了。

$WorkDir = "D:\Test\QR_ES\RG_Temp"
$BGDir = "D:\Test\QR_ES\3_BG"
$File = "D:\Test\QR_ES\4_Adr_Excel\KD_eMail.xlsx"
$SentDir = "D:\Test\QR_ES\RG_Temp\Sent\Dunning"

chdir $WorkDir

$firstPageList = Get-ChildItem "$WorkDir\1*.pdf" -File -Name

ForEach ($firstPage in $firstPageList)
{
$secondPage = "$BGDir\BG_RG.pdf"

$output = "Dunn-$firstPage"

invoke-command {pdftk $firstPage background $secondPage output $output}}

del 1*.pdf

gci $WorkDir\Dunn-*.pdf | rename-item -newname {$_.Name.Substring(5)} -Force

$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $false
$Workbook = $Excel.workbooks.open($file)

$DunnList = Get-ChildItem "$WorkDir\1*.pdf" -File -Name

ForEach ($Dunn in $DunnList)
{
$Worksheets = $Workbooks.worksheets
$Worksheet = $Workbook.Worksheets.Item("KD_eMail")
$Range = $Worksheet.Range("A1").EntireColumn
$DunnSearch = $Dunn.Substring(0,5)
$SearchString = $DunnSearch
$Search = $Range.find($SearchString)
$Recipient = $Worksheet.Cells.Item($Search.Row, $Search.Column + 1)

$Msg = "<span style='font-family:Calibri;font-size:12pt;'>Test</span>"

$Outlook = New-Object -ComObject Outlook.Application
$namespace = $Outlook.GetNameSpace("MAPI")
$namespace.Logon($null, $null, $false, $true)
$EmailFrom = ('test@test.com')
$account = $outlook.Session.Accounts.Item($EmailFrom)
$Mail = $Outlook.CreateItem(0)
$Mail.HTMLBody = $Msg
$Mail.Subject = "OP - $SearchString"

$Mail.To = $Recipient

function Invoke-SetProperty {
    param(
        [__ComObject] $Object,
        [String] $Property,
        $Value        
    )
    [Void] $Object.GetType().InvokeMember($Property,"SetProperty",$NULL,$Object,$Value)
   }
Invoke-SetProperty -Object $mail -Property "SendUsingAccount" -Value $account

$Mail.Attachments.Add("$WorkDir\$Dunn")
$Mail.Save()
$Mail.close(1)
$Mail.Send()}}

$workbook.close($false)

$Excel.Quit()

chdir $WorkDir

del 1*.pdf
thigvfpy

thigvfpy1#

请看这篇文章:https://stackoverflow.com/a/35955339/5329137,它不被接受为答案,但我相信这是关闭Excel的完整、正确的方法。

u4dcyp6a

u4dcyp6a2#

这就是为我做的事情:

$FilePID = (Get-Process -name Excel | Where-Object { $_.MainWindowTitle -like 'FileName.xlsx*' }).Id

$Workbook.Save()
$Workbook.close($false)

Stop-Process $FilePID
h9a6wy2h

h9a6wy2h3#

详细说明@asd的答案,因为MainWindowTitle不(总是)包括文件后缀(.xlsx),所以在将其与文件名进行比较时,您可能必须去掉后缀。我正在使用-REPLACE来使用最后一个点之前的所有内容的正则表达式匹配。

$excelPID = (Get-Process -name Excel | Where-Object { $_.MainWindowTitle -eq $fileName -replace '\.[^.]*$', '' }).Id
$workbook.Close()
Stop-Process $excelPID

相关问题