Excel UDF: check that workbook is opened successfully

[exolon]

I ran into a problem with the Excel UDF, where it was giving reporting "Error in expression". It turned out that using a relative path to open an Excel workbook works on some machines and fails on others (even with apparently the same version of Excel installed) - I'm not sure why.

In Excel.au3@181 we have:

If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)

This check will pass when given a relative path (i.e. "stuff.xls"), but Excel can fail to open the worksheet and leaves the $oExcel.ActiveWorkbook property unset, causing the script to die with the slightly unintuitive "error in expression" message.

We could fix this by either including the File UDF and taking the absolute path of the argument, or by testing that the workbook object exists after attempting to open one, or both:

(at the top)

#Include <File.au3>

(@line 179)

Func _ExcelBookOpen($sFilePath, $fVisible = 1, $fReadOnly = False, $sPassword = "", $sWritePassword = "")
    $sFilePath = _PathFull($sFilePath) ; get the canonical path, since it may be needed by Excel
    Local $oExcel = ObjCreate("Excel.Application")
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If $fVisible > 1 Then $fVisible = 1
    If $fVisible < 0 Then $fVisible = 0
    If $fReadOnly > 1 Then $fReadOnly = 1
    If $fReadOnly < 0 Then $fReadOnly = 0
    With $oExcel
        .Visible = $fVisible
        If $sPassword <> "" And $sWritePassword <> "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, $sWritePassword)
        If $sPassword = "" And $sWritePassword <> "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, Default, $sWritePassword)
        If $sPassword <> "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, Default)
        If $sPassword = "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly)
 
        ; check if the workbook has actually been opened
        If Not .ActiveWorkbook Then
            MsgBox(0, "Error", "Couldn't open workbook '" & $sFilePath & "'.")
            Return SetError(2, 0, 0)
        EndIf
 
        ; Select the first *visible* worksheet.
        For $i = 1 To .ActiveWorkbook.Sheets.Count
            If .ActiveWorkbook.Sheets($i).Visible = $xlSheetVisible Then
                .ActiveWorkbook.Sheets($i).Select()
                ExitLoop
            EndIf
        Next
    EndWith
    Return $oExcel
EndFunc   ;==>_ExcelBookOpen

How about it (or something like it)?

Edited September 19, 2011 by exolon