查找然后保存并关闭所有打开的Excel文档 [英] Find then save and close all open Excel documents

问题描述

我需要帮助来创建一个PowerShell脚本，该脚本将查找所有打开的Excel文档，然后首先，如果该文档是只读的，则不保存就关闭该文档，如果进行了更改，则不显示提示，或者如果不是只读的，则显示第二个提示，然后保存并关闭它们.该脚本将从预定任务中运行.

I need help creating a PowerShell script that will find all open Excel documents, then first if the document is read only close the document without saving and suppress prompts if changes were made, or second if the document is not read only then save and close them. This script will be run from a scheduled task.

之所以要这样做，是因为我需要一种自动的方式来在共享的生产区域计算机上保存和关闭文档.我们在生产车间的地板上大约有80台计算机，这些计算机的安装方式就像信息亭一样，供我们的生产工人访问执行其工作所需的程序和文档.这些计算机被组策略锁定，只能访问特定的应用程序.其中一个应用程序具有链接的Excel文档，供生产人员打开和编辑(它们从未创建文档).但是问题是，这些文档经常打开得太久，而且很多时候它们也没有保存.因此，我的解决方案是创建一个计划任务(在我们的生产班次之间运行)，以保存和关闭所有未打开的打开的Excel文档.

The reason why I want to do this is because I need an automated way of saving and closing documents on our shared production area computers. We have about 80 computers on our manufacturing shop floor area and these computers are setup like kiosks for our production workers to access programs and documents needed to do their work. These computers are locked down tight with group policy and only have access to specific applications. One of these applications has Excel documents linked within it for the production workers to open and edit (they don't ever create documents). But the problem is all too often these documents are left open far too long and many times they are also not saved. So my solution is to create a scheduled task (that runs in between our production shifts) to save and close all open Excel documents that are left open.

我已经在下面使用此PowerShell脚本关闭了其他应用程序，并且如果未进行任何更改，它也确实适用于Excel ...但是很明显，如果发生更改，您将得到保存提示，因此我需要一种保存方法Excel文档会在运行前首先显示(或者，如果只读，则取消提示并仅将其关闭).

I have used this PowerShell script below to close other applications and it does work for Excel too if no changes were made... but obviously you'll get a prompt to save if there are changes so I need a way to save the Excel documents first before this runs (or if read only suppress the prompt and just close).

Get-Process EXCEL | Foreach-Object { $_.CloseMainWindow() | Out-Null }

如果无法使用PowerShell完成此操作，则我愿意使用VBScript进行此操作.

If this can't be done with PowerShell I'm open to doing this with a VBScript instead.

推荐答案

@bgalea 有正确的想法，但答案很不完整.一方面，重新附加到COM对象仅在创建该对象的用户的上下文中起作用，因此您必须创建以该特定用户身份运行的计划任务或注销脚本(自动在该用户的用户身份中运行)退出时的上下文).

@bgalea had the right idea, but the answer is very incomplete. For one thing, re-attaching to COM objects only works in the context of the user who created the object, so you'd have to create either a scheduled task that runs as that particular user or a logoff script (automatically runs in a user's context at logoff).

由于您显然希望定期运行此命令，因此可能无法选择注销脚本，因此您可能希望使用登录脚本为每个用户创建各自的计划任务，例如像这样:

Since you apparently want to run this periodically, a logoff script probably isn't an option, so you may want to use a logon script to create a respective scheduled task for each user, e.g. like this:

schtasks /query /tn "Excel Cleanup" >nul 2>&1
if %errorlevel% neq 0 schtasks /create /tn "Excel Cleanup" /sc DAILY /tr "wscript.exe \"C:\path\to\your.vbs\"" /f

由这些任务运行的VBScript可能看起来像这样:

The VBScript run by these tasks might look somewhat like this:

On Error Resume Next
'attach to running Excel instance
Set xl = GetObject(, "Excel.Application")
If Err Then
  If Err.Number = 429 Then
    'Excel not running (nothing to do)
    WScript.Quit 0
  Else
    'unexpected error: log and terminate
    CreateObject("WScript.Shell").LogEvent 1, Err.Description & _
      " (0x" & Hex(Err.Number) & ")"
    WScript.Quit 1
  End If
End If
On Error Goto 0

xl.DisplayAlerts = False  'prevent Save method from asking for confirmation
                          'about overwriting existing files (when saving new
                          'workbooks)
                          'WARNING: this might cause data loss!

For Each wb In xl.Workbooks
  wb.Save
  wb.Close False
Next

xl.Quit
Set xl = Nothing

如果要使用PowerShell脚本而不是VBScript，则需要使用 GetActiveObject() 方法可附加到正在运行的Excel实例.

If you want a PowerShell script instead of VBScript you need to use the GetActiveObject() method to attach to a running Excel instance.

try {
  # attach to running Excel instance
  $xl = [Runtime.InteropServices.Marshal]::GetActiveObject('Excel.Application')
} catch {
  if ($_.Exception.HResult -eq -2146233087) {
    # Excel not running (nothing to do)
    exit 0
  } else {
    # unexpected error: log and terminate
    Write-EventLog -LogName Application `
      -Source 'Application Error' `
      -EventId 500 `
      -EntryType Error `
      -Message $_.Exception.Message
    exit 1
  }
}

$xl.DisplayAlerts = $false  # prevent Save() method from asking for confirmation
                            # about overwriting existing files (when saving new
                            # workbooks)
                            # WARNING: this might cause data loss!

foreach ($wb in $xl.Workbooks) {
  $wb.Save()
  $wb.Close($false)
}

$xl.Quit()
[void][Runtime.InteropServices.Marshal]::ReleaseComObject($xl)
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

根据 Microsoft文档

According to Microsoft's documentation the GetObject() method is supposed to work as well:

[void][Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic')
$xl = [Microsoft.VisualBasic.Interaction]::GetObject($null, 'Excel.Application')

但是当我尝试它时，我得到了一个额外的(隐藏的)Excel实例，而不是附加到已经在运行的实例中.

but when I tried it I ended up with an additional (hidden) Excel instance instead of attaching to the already running one.

注意:如果由于某种原因用户启动了多个Excel实例，则您需要为每个实例运行一次代码.

Note: If for some reason a user has started multiple Excel instances you will need to run the code once for each instance.

这篇关于查找然后保存并关闭所有打开的Excel文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！