{"id":69933,"date":"2005-04-27T21:51:00","date_gmt":"2005-04-27T21:51:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2005\/04\/27\/how-can-i-tell-if-an-excel-workbook-is-open-and-if-it-isnt-open-it\/"},"modified":"2005-04-27T21:51:00","modified_gmt":"2005-04-27T21:51:00","slug":"how-can-i-tell-if-an-excel-workbook-is-open-and-if-it-isnt-open-it","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-tell-if-an-excel-workbook-is-open-and-if-it-isnt-open-it\/","title":{"rendered":"How Can I Tell if an Excel Workbook is Open and, If It Isn\u2019t, Open It?"},"content":{"rendered":"

\"Hey, \n

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

— RK<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, RK. Every now and then we see a question and think, \u201cWe should answer this one; it\u2019s so easy it won\u2019t take more than a few minutes to finish the column and then we can all take a nice long lunch.\u201d At some point you\u2019d think we\u2019d learn that every time we say that it turns out that the problem isn\u2019t 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<\/I> learn, which is why we\u2019re still hard at work trying to answer this question.<\/P>\n

Of course, we still plan on taking a nice long lunch, but that\u2019s another story.<\/P>\n

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

Unfortunately, though, the Workbooks collection applies only to a specific instance of Excel; that meant we couldn\u2019t just create a new instance of Excel and get back a list of all<\/I> open workbooks. Instead, we\u2019d have to bind to each running instance of Excel, query its Workbooks collection, and then combine all those collections into a single list. That\u2019s theoretically possible, but\u2026.<\/P>\n

And so we thought to ourselves, \u201cOK, we\u2019re having difficulty solving a problem with Microsoft Excel. What do most people do when they\u2019re having problems with Excel?\u201d And once we put it that way, the answer was obvious: we\u2019d use Microsoft Word to extricate ourselves from our predicament.<\/P>\n

Yes, Microsoft Word. As it turns out, Word has something called the Tasks<\/B> collection that can replicate many of the functions of the Windows Task Manager. (If you\u2019re interested in learning more about the Tasks collection, take a look at the \u201cBuild Your Own Task Manager Using Microsoft Word\u201d<\/B><\/A> article on the Scripting for Microsoft Office<\/B><\/A> center.) As you can see here, open workbooks are displayed – by file name – in Task Manager:<\/P>\"Task \n


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:<\/P>

Set objShell = CreateObject(“Wscript.Shell”)<\/p>\n
Set objWord = CreateObject(“Word.Application”)\nSet colTasks = objWord.Tasks\ni = 0<\/p>\n
For Each objTask in colTasks\n    strName = LCase(objTask.Name)\n    If Instr(strName, “inventory.xls”) Then\n        i = 1\n    End If\nNext<\/p>\n
strCmdLine = “excel.exe ” & chr(34) & “C:\\Scripts\\Inventory.xls” & chr(34)<\/p>\n
If i = 0 Then\n    objShell.Run strCmdLine, 3\nEnd If<\/p>\n
objWord.Quit\n<\/PRE>\n
The script begins innocuously enough; we simply create an instance of the WSH Shell<\/B> object (we\u2019ll use that, if we need to, to open the workbook in question). We then create an instance of the Word.Application<\/B> object, and use the Tasks<\/B> property to retrieve the collection of tasks running on the computer. (Tasks include all the items you see on the Applications<\/B> tab in Task Manager.) We also set a variable named i to 0, for reasons we\u2019ll explain in a second.<\/P>\n
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\u2019s what we do here: <\/P>
strName = LCase(objTask.Name)\n<\/PRE>\n
We then use VBScript\u2019s InStr<\/B> function to see if the string inventory.xls<\/I> 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\u2019t, 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.<\/P>\n
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\u2019s what we do here:<\/P>
strCmdLine = “excel.exe ” & chr(34) & “C:\\Scripts\\Inventory.xls” & chr(34)\n<\/PRE>\n
Second, we use the Run<\/B> method to call this command string. In addition to the command string we pass Run one additional parameter: the value 3<\/B>, which means \u201cOpen this file in a maximized window, and then make it the active window.\u201d We then call the Quit<\/B> method to dismiss Microsoft Word (which, incidentally, never appears on screen) and then go about our business.<\/P>\n
And, yes, we\u2019re well aware of how crazy this all sounds, but it will work. Admittedly, it\u2019s not 100% foolproof; that\u2019s 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\u2019s a way to deal with that issue as well, but that will have to wait for another day.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I tell if a specific Excel workbook is open and, if it isn\u2019t, open it?— RK Hey, RK. Every now and then we see a question and think, \u201cWe should answer this one; it\u2019s so easy it won\u2019t take more than a few minutes to finish the column and then […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[48,49,3,5,395,714],"class_list":["post-69933","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript","tag-word-application","tag-wshshell"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I tell if a specific Excel workbook is open and, if it isn\u2019t, open it?— RK Hey, RK. Every now and then we see a question and think, \u201cWe should answer this one; it\u2019s so easy it won\u2019t take more than a few minutes to finish the column and then […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/69933","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=69933"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/69933\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=69933"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=69933"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=69933"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}