Check if a specific Excel file is open when several Excel files are open and activate it

vbscript

Edit: Before I put a specific Excel file or its window to the front I need to check whether it's running/still open.

Old Question:
I want to set a specific Excel window to the front.

With this VBScript code I can activate one single Excel window by its name. But since more than one Excel window are opened it doesn't work anymore. In this case it won't find the needed window and it can't be checked if it's open. So it will always say that the ExcelFileName is not open.

Set WshShell = WScript.CreateObject ("WScript.Shell")
if WshShell.AppActivate(ExcelFileName) = True then
    wscript.echo ExcelFileName & " is opened."
    WshShell.sendkeys "%x" 'in Excel 2003 this would had opened the extra-menu-droplist of the menu-bar. Now it just activates Excel.
else
    wscript.echo ExcelFileName & " is not open."
End if

How can I make it work with more than one opened Excel window?

Best Answer

So you want to detect if a workbook with a given name is opened? That can be done like this in VBScript:

ExcelFileName = "some.xlsx"

On Error Resume Next
Set xl = GetObject(, "Excel.Application")  'attach to running Excel instance
If Err Then
  If Err.Number = 429 Then
    WScript.Echo "Workbook not open (Excel is not running)."
  Else
    WScript.Echo Err.Description & " (0x" & Hex(Err.Number) & ")"
  End If
  WScript.Quit 1
End If
On Error Goto 0

Set wb = Nothing
For Each obj In xl.Workbooks
  If obj.Name = ExcelFileName Then  'use obj.FullName for full path
    Set wb = obj
    Exit For
  End If
Next
If wb Is Nothing Then
  WScript.Echo "Workbook not open."
  WScript.Quit 1
End If

GetObject can only attach to the Excel instance that was launched first, though. You'd need to terminate that instance to be able to attach to the next one (see here). However, since it's the default to open workbooks in an already running instance, the above should work for most scenarios.

Related Topic