How Can I Tell if an Excel Workbook is Open and, If It Isn’t, Open It?

ScriptingGuy1

Hey, Scripting Guy! How can I tell if a specific Excel workbook is open and, if it isn’t, open it?

— RK

Hey, RK. Every now and then we see a question and think, “We should answer this one; it’s so easy it won’t take more than a few minutes to finish the column and then we can all take a nice long lunch.” At some point you’d think we’d learn that every time we say that it turns out that the problem isn’t as easy as it first looked, and that the column takes far more than just a few minutes to complete. But, no, we never do learn, which is why we’re still hard at work trying to answer this question.

Of course, we still plan on taking a nice long lunch, but that’s another story.

How did we get fooled on this one? Well, we knew Excel has a Workbooks collection that returns a list of all the open workbooks. Foolishly, we assumed this meant all the open workbooks. (And, yes, we know what people say about assuming things.)

Unfortunately, though, the Workbooks collection applies only to a specific instance of Excel; that meant we couldn’t just create a new instance of Excel and get back a list of all open workbooks. Instead, we’d have to bind to each running instance of Excel, query its Workbooks collection, and then combine all those collections into a single list. That’s theoretically possible, but….

And so we thought to ourselves, “OK, we’re having difficulty solving a problem with Microsoft Excel. What do most people do when they’re having problems with Excel?” And once we put it that way, the answer was obvious: we’d use Microsoft Word to extricate ourselves from our predicament.

Yes, Microsoft Word. As it turns out, Word has something called the Tasks collection that can replicate many of the functions of the Windows Task Manager. (If you’re interested in learning more about the Tasks collection, take a look at the “Build Your Own Task Manager Using Microsoft Word” article on the Scripting for Microsoft Office center.) As you can see here, open workbooks are displayed – by file name – in Task Manager:

We figured we could use the Tasks collection in Word to check and see if a particular workbook was open and, if not, open it. All of which gave rise to one of the craziest workarounds the Scripting Guys have ever come up with:

Set objShell = CreateObject(“Wscript.Shell”)

Set objWord = CreateObject(“Word.Application”)
Set colTasks = objWord.Tasks
i = 0
For Each objTask in colTasks
    strName = LCase(objTask.Name)
    If Instr(strName, “inventory.xls”) Then
        i = 1
    End If
Next
strCmdLine = “excel.exe ” & chr(34) & “C:\Scripts\Inventory.xls” & chr(34)
If i = 0 Then
    objShell.Run strCmdLine, 3
End If
objWord.Quit

The script begins innocuously enough; we simply create an instance of the WSH Shell object (we’ll use that, if we need to, to open the workbook in question). We then create an instance of the Word.Application object, and use the Tasks property to retrieve the collection of tasks running on the computer. (Tasks include all the items you see on the Applications tab in Task Manager.) We also set a variable named i to 0, for reasons we’ll explain in a second.

Next we loop through the collection of tasks. We grab a task and store the lowercase version of the task name in a variable named strName; that’s what we do here:

strName = LCase(objTask.Name)

We then use VBScript’s InStr function to see if the string inventory.xls can be found anywhere in the task name. If it is, that means Inventory.xls is already open and we set the value of i to 1. If it isn’t, then Inventory.xls must not be open, and the value of i stays at 0. We then loop around and continue with all the remaining tasks.

When we exit the loop we check the value of i. If i is equal to 0 we do two things. First we create a command string that calls Excel.exe, passing as a command-line parameter the path to Inventory.xls. That’s what we do here:

strCmdLine = “excel.exe ” & chr(34) & “C:\Scripts\Inventory.xls” & chr(34)

Second, we use the Run method to call this command string. In addition to the command string we pass Run one additional parameter: the value 3, which means “Open this file in a maximized window, and then make it the active window.” We then call the Quit method to dismiss Microsoft Word (which, incidentally, never appears on screen) and then go about our business.

And, yes, we’re well aware of how crazy this all sounds, but it will work. Admittedly, it’s not 100% foolproof; that’s because only the file names are captured by the Tasks collection, not the complete file paths. That could be a problem if you have the file D:\Archive\Old Inventory Files\January 2004\Inventory.xls open; the script will see only the file name – Inventory.xls – and assume that C:\Scripts\Inventory.xls is open. We think there’s a way to deal with that issue as well, but that will have to wait for another day.

ScriptingGuy1

Follow