CODEWITHSUNDEEP
excelpowershellvbscript
Jeff Martinson
Jeff Martinson

Reputation: 23

Find then save and close all open Excel documents

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.

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.

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 }

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

Upvotes: 2

Views: 3885

Answers (2)

user5721973
user5721973

Reputation: 

Set xlBook = GetObject("C:\Users\David Candy\Documents\Super.xls", "Excel.Application")
For each wsheet in xlbook.worksheets
    msgbox wsheet.name
next

VBScript, JScript, PS, VBA can all do it. Above is VBScript (and thus also VBA). The technology is COM not a language.

EDIT

As said using the app object doesn't work in excel.

A excel file opened.

EXCEL.EXE                    13560 Console                    1     19,228 K Running         DESKTOP-UCDGI39\David Candy
                             0:00:00 Microsoft Excel - Super.xls

Running GetObject using Excel.Application

EXCEL.EXE                    13560 Console                    1     19,236 K Running         DESKTOP-UCDGI39\David Candy
                             0:00:00 Microsoft Excel - Super.xls
EXCEL.EXE                    15124 Console                    1     12,772 K Running         DESKTOP-UCDGI39\David Candy
                             0:00:00 N/A

NOTE

  • Separate process
  • Not started with COM switches (/a or /automation and /embedding)
  • No access to the other excel

This is in addition to other faulty behaviour by excel where it won't exit memory when references are released.

Upvotes: 0

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200293

@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

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

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()

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')

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

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

Upvotes: 4

Related Questions