_Excel_BookOpen Unhandled COM Error

[seadoggie01]

I (unintentionally) tried opening a Workbook from two folders with the same name (which you can't do in Excel) using a script like this: 

Local $oExcel = _Excel_Open()

Local $oBook1 = _Excel_BookOpen($oExcel, "C:\1\Book.xlsx")
If @error then Exit ConsoleWrite("Failed to open book 1!" & @CRLF)

Local $oBook2 = _Excel_BookOpen($oExcel, "C:\2\Book.xlsx")
If @error then Exit ConsoleWrite("Failed to open book 2!" & @CRLF)

I was surprised to see that the UDF contained the error instead of handling it though. Because Excel will happily execute $oExcel.Workbooks.Open(...) and doesn't throw a COM error, _Excel_BookOpen continues with it's code and assumes that $oWorkbook is an object. The next line uses $oWorkbook.Name and AutoIt exits with an error:

"C:\Program Files (x86)\AutoIt3\Include\Excel.au3" (233) : ==> The requested action with this object has failed.:
Local $oWindow = $oExcel.Windows($oWorkbook.Name)
Local $oWindow = $oExcel.Windows($oWorkbook^ ERROR

The thing I found interesting is that VarGetType still returns "Object" on $oWorkbook while IsObj returns false.

Anyways, to fix this issue, I suggest the following change to _Excel_BookOpen:

Func _ExcelEx_BookOpen($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default, $bUpdateLinks = Default)
    ; Error handler, automatic cleanup at end of function
    Local $oError = ObjEvent("AutoIt.Error", "__Excel_COMErrFunc")
    If @error Then Exit Debug("Failed to capture COM Errors!")
    #forceref $oError
    If Not IsObj($oExcel) Or ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, @error, 0)
    If StringLeft($sFilePath, "HTTP") = 0 And Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If $bReadOnly = Default Then $bReadOnly = False
    If $bVisible = Default Then $bVisible = True
    Local $oWorkbook = $oExcel.Workbooks.Open($sFilePath, $bUpdateLinks, $bReadOnly, Default, $sPassword, $sWritePassword)
    If @error Then Return SetError(3, @error, 0)
    If Not IsObj($oWorkbook) Then Return SetError(4, 0, False)
    Local $oWindow = $oExcel.Windows($oWorkbook.Name)
    If IsObj($oWindow) Then $oWindow.Visible = $bVisible
    ; If a read-write workbook was opened read-only then set @extended = 1
    If $bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(0, 1, $oWorkbook)
    Return $oWorkbook
EndFunc   ;==>_Excel_BookOpen

Adding "If Not IsObj($oWorkbook) Then Return SetError(4, 0, False)" seems to follow the coding convention

Edited May 15, 2020 by seadoggie01
Added console output for Error

[Danp2]

19 minutes ago, seadoggie01 said:

If Not IsObj($oWorkbook) Then Return SetError(4, 0, False)

In my brief tests, this one line would be sufficient. No need to add a COM error handler.

[seadoggie01]

1 minute ago, Danp2 said:

No need to add a COM error handler.

There is already a COM error handler, which is what I thought was very strange. The first line of the function declares it but I think because the object is invalid it doesn't work. I don't claim to understand how ObjEvent works though  

[Danp2]

The code you posted doesn't match my version of _Excel_BookOpen. Is _ExcelEx_BookOpen from a different UDF?

[seadoggie01]

Uh... nope. I copied _Excel_BookOpen, changed the name of the function, and added that line you posted. I'm using AutoIt version 3.3.14.5

[Danp2]

Me too... I'm just going coocoo

[seadoggie01]

I think we all are right now 

[water]

I just tested and - strange enough - Excel doesn't return a COM error.
I will add the check for object as you suggested.
Which description of @error = 4 should I add to th help file?
"Excel didn't return a Workbook object. Caused by the inability of Excel to open two Workbooks with the same name at a time"

Edit:
Just tested _Excel_BookOpenText: This function needs no modification as it returns COM error 0x8002009

Edited May 15, 2020 by water

[Danp2]

22 minutes ago, water said:

Which description of @error = 4 should I add to th help file?
"Excel didn't return a Workbook object. Caused by the inability of Excel to open two Workbooks with the same name at a time"

Maybe something more generic in case there are other causes of the same issue. Something like "Excel didn't return a Workbook object. See remarks for possible causes"

[water]

Version 2: "Excel didn't return a Workbook object. Could be caused by the inability of Excel to open two Workbooks with the same name at a time"

[water]

Done