Jump to content
Sign in to follow this  

Listing Open Excel Workbooks

Recommended Posts

Hello, all. I'm going round and round with this one.

I have a number of scripts which gather data, open Excel files the user chooses, write date, set formatting, save the worksheet and close it.

This only works if the workbook is closed. I understand this is a known issue. I test for the worksheet being open by seeing if I can open the file for writing, then warn the user.

I would like to take the scripts up a notch, and let the user choose from worksheets which are already open. I understand _ExcelBookAttach() will let me access those. The question is, how can I get a list of what workbooks are open?

I've tried using COM with ObjGet() to get the Application object and list names, but the script keeps failing, saying "The requested action with this object has failed" and pointing to a piece of my COM code. I've tried different things, so the failure point keeps changing.

Does anyone have any code examples which can point me down the right direction? The MSDN articles on COM are semi-helpful, but they assume I have more knowledge than I do, and that I'm coding in Visual Basic.

Share this post

Link to post
Share on other sites

Most of this is from the helpfile, does it not work for you?

Local $oExcel = ObjGet("", "Excel.Application") ; Get an existing Excel Object
If @error Then
    MsgBox(0, "ExcelTest", "Error Getting an active Excel Object. Error code: " & Hex(@error, 8))

For $oWB in $oExcel.Workbooks

Share this post

Link to post
Share on other sites

That does work, thank you. That will be quite handy.

Now I'm having trouble attaching to the existing worksheet. I'm using a simple test program.


$ojbOpen = _ExcelBookOpen("D:UsersjryanDropboxAvidProjectsKLRUKLRU Upgrade Project Log.xlsx", 0)

;$ojbOpen = _ExcelBookAttach("D:UsersjryanDropboxAvidProjectsKLRUKLRU Upgrade Project Log.xlsx")

;If @error Then

; MsgBox(0,"Closed","The file is not open.")


; MsgBox(0,"Open","The file is open.")


If $ojbOpen.ActiveWorkbook.ReadOnly Then

MsgBox(0,"Open","It's already open")


$ojbOpen = _ExcelBookAttach("D:UsersjryanDropboxAvidProjectsKLRUKLRU Upgrade Project Log.xlsx")

If @error Then MsgBox(0,"Error","Unable to attach to open worksheet - error " & @error)


MsgBox(0,"CLosed","It's not open")




$ojbOpen.Visible = True



MsgBox(0,"Killing time","Just holding the program open")



Which works when the workbook starts closed, but not when it's open. I get a error:

D:UsersjryanDropboxMy AutoITSite ProfilerOpenBooksTest.au3 (23) : ==> The requested action with this object has failed.:

$ojbOpen.Visible = True

$ojbOpen.Visible = True^ ERROR

My testing correctly detects whether the workbook is open or not. But then I close the instance I opened, and try to attach to the existing instance, and it does not work.

Where am I going off course here?


Share this post

Link to post
Share on other sites

Part of the problem is that through most of your script the $objOpen object is the object tied to the excel application itself. You'll see it named $oExcel in most of the source code around here. "$oExcel.Visible = 1" is a valid statement. Later in your script you assign the result from _ExcelBookAttach() to the same $objOpen variable. _ExcelBookAttach() returns an object to a workbook overwriting the former object to the application. So what "$objOpen.Visible = 1" really says at that point is "$oExcel.Workbook.Visible = 1" which is not a valid statement.

You might also have to activate the workbook before you can access or close it.

I tend not to use the UDF for complicated excel stuff, although it's a great place to steal code from.

#include <Excel.au3>
Global $sFile = "test.xls"

$oExcel = ObjGet("", "Excel.Application") ; Get object to an existing Excel application
If @error Then ; Excel not running, start it and open workbook
$oExcel = ObjCreate("Excel.Application") ; Get object to a new Excel application
Else ; Excel already running, activate workbook if already open, else open it
For $oWB in $oExcel.Workbooks
     If $oWB.Name = $sFile Then
$oExcel.Visible = 1
Edited by Spiff59

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...