Sign in to follow this  
Followers 0
exolon

Excel UDF: check that workbook is opened successfully

1 post in this topic

#1 ·  Posted (edited)

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 by exolon

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