_excelSheetActivate() causes COM error

[sigil]

I'm trying to activate a sheet in an Excel workbook. But when I run _excelSheetActivate(), I get an "unknown name" COM error.

Here's the code:

#include <excel.au3>

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

$eObj=_excelbookattach($myfilepath)

_excelsheetactivate($eobj,$mySheet)


Func MyErrFunc() 
   $HexNumber=hex($oMyError.number,8) 
   Msgbox(0,"","We intercepted a COM Error !" & @CRLF & _
                "Number is: " & $HexNumber & @CRLF & _
                "Windescription is: " & $oMyError.windescription ) 

   $g_eventerror = 1 
Endfunc

I was curious about how _ExcelSheetActivate() works, so I looked at Excel.au3 and saw that it calls

$oExcel.ActiveWorkbook.Sheets.Count

where $oExcel is the Excel object passed as the first parameter. I tried to Msgbox() this sheet count property, but got the "unknown name" error. Then I rewrote it as

$oExcel.Sheets.Count

and it gave me the correct worksheet count.

Is there something about the way my Excel is set up that makes .ActiveWorkbook (which is used all over the UDF) unnecessary?

I'm using AutoIt version 3.3.6.1.

[Tvern]

Some observations:

I am getting the same error when I run your script using Office 2007 (should have stayed with 2003) on Windows 7 Ultimate x64. What is your setup?

I am not getting the error when I use _ExcelBookOpen() instead of _ExcelBookAttach().

You should repair your shift key

[sigil]

Some observations:

I am getting the same error when I run your script using Office 2007 (should have stayed with 2003) on Windows 7 Ultimate x64. What is your setup?

I am not getting the error when I use _ExcelBookOpen() instead of _ExcelBookAttach().

You should repair your shift key

I'm using Office 2003 with XP.

Yes, it works with _ExcelBookOpen(), but I already have the workbook open, don't want to open another copy.

[sigil]

I think the difference is that _ExcelBookOpen() returns an Application object, whereas _ExcelBookAttach returns a Workbook object. This is a puzzling inconsistency; maybe some versions of Excel treat both objects identically. Apparently mine doesn't.

[picaxe]

This works for me on Excel 2003, WinXP

$eObj = _ExcelBookAttach($myfilepath)
$eObj.Sheets($mySheet).Select()

[sigil]

This works for me on Excel 2003, WinXP

$eObj = _ExcelBookAttach($myfilepath)
$eObj.Sheets($mySheet).Select()

Right, so that means that _ExcelSheetActivate() is buggy, and needs to have that .Sheets($mySheet) inserted on the next release.

[PsaltyDS]

Right, so that means that _ExcelSheetActivate() is buggy, and needs to have that .Sheets($mySheet) inserted on the next release.

Meh. Buggy is too strong a term. Let's go with poorly documented.

The real problem is that _ExcelBookAttach() returns a .Workbook object, while _ExcelBookNew() and _ExcelBookOpen() both return the new .Application object. This is because most of the UDF assumes/hopes the eponymous $oExcel is an .Application object. This doesn't match up with the purpose of _ExcelBookAttach(), which is to pick out one among possibly many workbooks that might all be under the same .Application.Workbooks collection.

Better documentation for _ExcelBookAttach() would capture the object to something like $oWkBk vice $oExcel in the example to help make the distinction. Here is a modified version of Example 1 from the help file:

; ********************
; Modified Example 1
; ********************
#include <Excel.au3>
#include <File.au3>

$sFilePath = @TempDir & "\Temp.xls"
If Not _FileCreate($sFilePath) Then ;Create an .XLS file to attach to
    MsgBox(4096, "Error", " Error Creating File - " & @error)
EndIf

$oExcel = _ExcelBookOpen($sFilePath)
_ExcelWriteCell($oExcel, "If you can read this, then Success!", 1, 1) ;Write to the Cell
ConsoleWrite("$oExcel type = " & ObjName($oExcel) & @LF) ; "_Application"

$oWkBk = _ExcelBookAttach($sFilePath) ;with Default Settings ($s_mode = "FilePath" ==> Full path to the open workbook)
ConsoleWrite("$oWkBk type = " & ObjName($oWkBk) & @LF) ; "_Workbook"

MsgBox(0, "Exiting", "Press OK to Save File and Exit")
_ExcelBookClose($oWkBk, 1, 0) ;This method will save then Close the file, without any of the normal prompts, regardless of changes

Edited June 3, 2010 by PsaltyDS

[sigil]

Meh. Buggy is too strong a term. Let's go with poorly documented.

The real problem is that _ExcelBookAttach() returns a .Workbook object, while _ExcelBookNew() and _ExcelBookOpen() both return the new .Application object. This is because most of the UDF assumes/hopes the eponymous $oExcel is an .Application object. This doesn't match up with the purpose of _ExcelBookAttach(), which is to pick out one among possibly many workbooks that might all be under the same .Application.Workbooks collection.

Shouldn't they all return a .Workbook object? That would be in keeping with their names. Then the rest of the UDF could start off with something like

if isWorkbook($obj) then
 $appObj=$obj.Application
else
 $appObj=$obj
endif

and then just use $appObj for the rest of the function. Users who don't make object calls (i.e., they only manipulate Excel through the UDF) wouldn't notice the difference, and those who do would benefit from the consistent naming scheme.

[PsaltyDS]

Yes, there are multiple reasons for a rewrite on the Excel UDF, and this is one of them. (Better use of COM arrays also.)

I suggest you start by creating a new topic for your version on the forum and posting suggested changes there...