Listing Open Excel Workbooks

[newsman220]

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.

[Spiff59]

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))
    Exit
EndIf

For $oWB in $oExcel.Workbooks
    MsgBox(0,"",$oWB.Name)
Next

[newsman220]

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.

expandcollapse popup

#include 



$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.")

;Else

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

;EndIf

If $ojbOpen.ActiveWorkbook.ReadOnly Then

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

_ExcelBookClose($ojbOpen)

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

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

Else

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

MsgBox(0,"Name",$ojbOpen.ActiveWorkbook.FullName)

MsgBox(0,"Name",$ojbOpen.ActiveWorkbook.Name)

EndIf

$ojbOpen.Visible = True

$ojbOpen.ActiveWorkbook.Sheets(4).Select

$ojbOpen.ActiveSheet.Columns(2).Insert()

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

_ExcelBookSave($ojbOpen)

_ExcelBookClose($ojbOpen)

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?

Thanks.

[Spiff59]

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
$oExcel.Workbooks.Open($sFile)
Else ; Excel already running, activate workbook if already open, else open it
For $oWB in $oExcel.Workbooks
     If $oWB.Name = $sFile Then
         $oWB.Activate()
         ExitLoop
     EndIf
Next
$oExcel.Workbooks.Open($sFile)
EndIf
$oExcel.Visible = 1
Sleep(5000)
;$oExcel.Workbooks.Save
$oExcel.Workbooks.Close
$oExcel.Quit

Edited September 20, 2012 by Spiff59