Sign in to follow this  
Followers 0
sigil

_excelSheetActivate() causes COM error

9 posts in this topic

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.

Share this post


Link to post
Share on other sites

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 :mellow:

Share this post


Link to post
Share on other sites

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 :mellow:

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

This works for me on Excel 2003, WinXP

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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

:mellow:

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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...

:mellow:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

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  
Followers 0