_Excel_RangeRead catch exception from CUI exe

[Barry1975]

Hi Im new to AutoIt,

I want to be able to catch an error in the excel where the cellname is incorrect. This works in Script mode (sciTe --> go) However when compiled in exe with CUI then the exception is not handled anymore. Who knows what i am doing wrong

 

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

Local $bAttached = False

$oExcel = _Excel_Open() ;opens a new instance of the Excel software
if $oExcel == 0 then
  ConsoleWrite("Unable to open MS Excel")
   exit
endif

; Get workbook
$workbook  = _Excel_BookAttach($excelfile) ;with $s_mode = "Title" ==> Title of the Excel window
If @error Then
 $workbook = _Excel_BookOpen($oExcel, $excelfile)
 If @error Then
   ConsoleWrite("Unable to open Workbook")
   _Excel_Close($oExcel)
   Exit
 Endif
$bAttached = False
Else
$bAttached = True
EndIf

 

Local $sResult = ""
$sResult =_Excel_RangeRead($Workbook, Default, "B??",3)

If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Catches the error as Error 3 as external com error

; When compiled in as Exe Error is not catched and displays Error; The requested action with this object has failed.

 

Func MyErrFunc($oMyError)
Local $HexNumber
Local $strMsg
$HexNumber = Hex($oMyError.Number, 8)
$strMsg = "Error Number: " & $HexNumber & @CRLF
$strMsg &= "WinDescription: " & $oMyError.WinDescription & @CRLF
$strMsg &= "Script Line: " & $oMyError.ScriptLine & @CRLF
msgbox("","Error",  $strMsg)
SetError(1)
Endfunc

 

[water]

Line 255 is

$oWorkbook.Save()

in function function _Excel_BookSave.
Seems you didn't post the whole script?

[Barry1975]

No I did not because I thought it would be to much info.

How do you know line 255 is in _Excel_Booksave ?

I like autoit, but find it difficult to handle exceptions

 

The Whole script :

Global $oMyError = ObjEvent("AutoIt.Error","MyErrFunc")
#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Outfile=xls_Getcell_textX86.exe
#AutoIt3Wrapper_Outfile_x64=GetcellX64.exe
#AutoIt3Wrapper_Change2CUI=y
#AutoIt3Wrapper_Add_Constants=n
#AutoIt3Wrapper_Run_Au3Stripper=y
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****

 

#include <Excel.au3>
#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <TrimFunctions.au3>

$excelfile = ""
$excelsheet = ""
$excelcell = ""
Local $bAttached = True

$combined = ""
For $i = 1 To $CmdLine[0]
 $combined = $combined & " " & $CmdLine[$i]
Next

$params = $combined

if StringInStr ( $params, "/" ) Then
 Local $aParam = StringSplit($params, "/") ; Split the string of days using the delimiter "," and the default flag value.

    For $i = 1 To $aParam[0] ; Loop through the array returned by StringSplit to display the individual values.

 if StringInStr ( $aParam[$i], "file:" ) Then
     $excelfile = StringReplace($aParam[$i], "file:", "")
    elseif StringInStr ( $aParam[$i], "sheet:" ) Then
      $excelsheet = StringReplace($aParam[$i], "sheet:", "")
  elseif StringInStr ( $aParam[$i],  "cell:" ) Then
      $excelcell =  StringReplace($aParam[$i], "cell:", "")
    endIf
    Next

EndIf

 $excelfile = _Alltrim($excelfile)
 $excelsheet = _Alltrim($excelsheet)
 $excelcell = _Alltrim($excelcell)

; Temp override vars
$excelfile = "f:\test1.xlsm"
$excelsheet = "2016"
$excelcell = "B?"

$oExcel = _Excel_Open() ;opens a new instance of the Excel software
if $oExcel == 0 then
  ConsoleWrite("Unable to open MS Excel")
   exit
endif

; Get workbook
$workbook  = _Excel_BookAttach($excelfile) ;with $s_mode = "Title" ==> Title of the Excel window
If @error Then
 $workbook = _Excel_BookOpen($oExcel, $excelfile)
 If @error Then
   ConsoleWrite("Unable to open Workbook")
   _Excel_Close($oExcel)
   Exit
 Endif
 Local $bAttached = False
Else
 Local $bAttached = True
EndIf

; Set excel sheet search sheet first
if $excelsheet <> "" Then
 $aArray = _Excel_SheetList($oExcel)
 $iIndex =  _ArraySearch($aArray,$excelsheet,0,0,0)
 if $iIndex <> "-1" Then
  $oExcel.Sheets($excelsheet).select
 EndIf
EndIf

Local $sResult = ""

$sResult =_Excel_RangeRead($Workbook, Default, "" & $excelcell & "",3) ; return 1 = value ; 3 == text
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ConsoleWrite($sResult)

if $bAttached = True Then
 Exit
Endif

_Excel_Close($oExcel,False,True) ; closes the instance of the Excel software
;_Excel_Close($oExcel,False) ; closes the instance of the Excel software

Func Writeparams()

$strMsg = "xlsReadcellval: /file:[filename] /sheet:[sheetname] /cell:[cellname]"  & @CRLF & @CRLF
$strMsg &= "[filename] the path of the excelfile + extension e.g. f:\tmp\report.xlsm" & @CRLF
$strMsg &= "[sheetname] optional the name of the excell sheet to open, default is main sheet" & @CRLF
$strMsg &= "[cellname] the name of the cell e.g. B2 do not use 2,2" & @CRLF
ConsoleWrite($strMsg)
Endfunc

Func MyErrFunc($oMyError)
Local $HexNumber
Local $strMsg
$HexNumber = Hex($oMyError.Number, 8)
$strMsg = "Error Number: " & $HexNumber & @CRLF
$strMsg &= "WinDescription: " & $oMyError.WinDescription & @CRLF
$strMsg &= "Script Line: " & $oMyError.ScriptLine & @CRLF
msgbox("","Error",  $strMsg)
SetError(1)
Endfunc

 

 

[water]

1 hour ago, Barry1975 said:

How do you know line 255 is in _Excel_Booksave ?

Because the first UDF you included is the Excel UDF and so I checked line 255 in this UDF.

[water]

BTW: Could you please tag your code (use the <> icon in the editor)? makes it easier to read and adds line numbers plus highlighting.

[water]

Which version of AutoIt do you run?

[Barry1975]

I am running version : 3.3.14.2

I Created a workaround by checking if the cell name is valid this solves the problem. Sorry for the ugly code but as mentioned only using AutoIt for 2 days now. So I am missing knowledge of functions I can use.

 

expandcollapse popup

If _ValidCell($excelcell) = True Then
Local $sResult = ""
$sResult =_Excel_RangeRead($Workbook, Default, "" & $excelcell & "",3) ; return 1 = value ; 3 == text
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
ConsoleWrite($sResult)
EndIf



Func _ValidCell($scell)
 $bValid = True
 $bStartNo = False
 $bStartChar = False
 $bValidchar = False


For $iChar In StringToASCIIArray($scell)
    $bValidchar = False

 Switch $iChar
 Case 48 To 57
   $bStartNo = True
   $bValidchar = True
   if $bStartChar = False Then
    Return False
   EndIf
   ; number
  Case 65 To 90
   if $bStartNo = True Then
    Return False
   EndIf
   $bStartChar = True
   $bValidchar = True
  Case 97 To 122
   if $bStartNo = True Then
    Return False
   EndIf
    $bStartChar = True
   $bValidchar = True
 EndSwitch

 If $bValidchar = False Then
  Return False
 EndIf

Next


 Return $bValid
EndFunc   ;==>_StringBetween

 

[Barry1975]

Here the complete working code for people who can use it. And for experts to suggest improvements to learn from.

expandcollapse popup

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Outfile=xls_Getcell_textX86.exe
#AutoIt3Wrapper_Outfile_x64=GetcellX64.exe
#AutoIt3Wrapper_Change2CUI=y
#AutoIt3Wrapper_Add_Constants=n
#AutoIt3Wrapper_Run_Au3Stripper=y
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
Global $oMyError = ObjEvent("AutoIt.Error","MyErrFunc")



#include <Excel.au3>
#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <TrimFunctions.au3>

$excelfile = ""
$excelsheet = ""
$excelcell = ""
Local $bAttached = True

$combined = ""
For $i = 1 To $CmdLine[0]
    $combined = $combined & " " & $CmdLine[$i]
Next

$params = $combined

if StringInStr ( $params, "/" ) Then
    Local $aParam = StringSplit($params, "/") ; Split the string of days using the delimiter "," and the default flag value.

    For $i = 1 To $aParam[0] ; Loop through the array returned by StringSplit to display the individual values.
    if StringInStr ( $aParam[$i], "file:" ) Then
           $excelfile = StringReplace($aParam[$i], "file:", "")
       elseif StringInStr ( $aParam[$i], "sheet:" ) Then
            $excelsheet = StringReplace($aParam[$i], "sheet:", "")
        elseif StringInStr ( $aParam[$i],  "cell:" ) Then
            $excelcell =  StringReplace($aParam[$i], "cell:", "")
       endIf
    Next

EndIf

 $excelfile = _Alltrim($excelfile)
 $excelsheet = _Alltrim($excelsheet)
 $excelcell = _Alltrim($excelcell)

; Temp override vars
;$excelfile = "f:\test1.xlsm"
;$excelsheet = "2016"
;$excelcell = "B?"

$oExcel = _Excel_Open() ;opens a new instance of the Excel software
if $oExcel == 0 then
  ConsoleWrite("Unable to open MS Excel")
   exit
endif


; Get workbook
$workbook  = _Excel_BookAttach($excelfile) ;with $s_mode = "Title" ==> Title of the Excel window
If @error Then
    $workbook = _Excel_BookOpen($oExcel, $excelfile)
    If @error Then
            ConsoleWrite("Unable to open Workbook")
            _Excel_Close($oExcel)
            Exit
    Endif
    Local $bAttached = False
Else
    Local $bAttached = True
EndIf

; Set excel sheet search sheet first
if $excelsheet <> "" Then
    $aArray = _Excel_SheetList($oExcel)
    $iIndex =  _ArraySearch($aArray,$excelsheet,0,0,0)
    if $iIndex <> "-1" Then
        $oExcel.Sheets($excelsheet).select
    EndIf
EndIf



; Test if cell is a valida excel named cel
If _ValidCell($excelcell) = True Then
Local $sResult = ""
$sResult =_Excel_RangeRead($Workbook, Default, "" & $excelcell & "",3)  ; return 1 = value ; 3 == text
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
ConsoleWrite($sResult)
EndIf


if $bAttached = True Then
    Exit ;exit without closing
Endif

_Excel_Close($oExcel,False,True) ; closes the instance of the Excel software



Func Writeparams()

$strMsg = "xlsReadcellval: /file:[filename] /sheet:[sheetname] /cell:[cellname]"  & @CRLF & @CRLF
$strMsg &= "[filename] the path of the excelfile + extension e.g. f:\tmp\report.xlsm" & @CRLF
$strMsg &= "[sheetname] optional the name of the excell sheet to open, default is main sheet" & @CRLF
$strMsg &= "[cellname] the name of the cell e.g. B2 do not use 2,2" & @CRLF
ConsoleWrite($strMsg)
Endfunc



Func _ValidCell($scell)
    $bValid = True
    $bStartNo = False
    $bStartChar = False
    $bValidchar = False

For $iChar In StringToASCIIArray($scell)
    $bValidchar = False

    Switch $iChar
    Case 48 To 57
            $bStartNo = True
            $bValidchar = True
            if $bStartChar = False Then
                Return False
            EndIf
            ; number
        Case 65 To 90
            if $bStartNo = True Then
                Return False
            EndIf
            $bStartChar = True
            $bValidchar = True
        Case 97 To 122
            if $bStartNo = True Then
                Return False
            EndIf
                $bStartChar = True
            $bValidchar = True
    EndSwitch

    If $bValidchar = False Then
        Return False
    EndIf

Next


    Return $bValid
EndFunc   ;==>_StringBetween



Func MyErrFunc($oMyError)
Local $HexNumber
Local $strMsg
$HexNumber = Hex($oMyError.Number, 8)
$strMsg = "Error Number: " & $HexNumber & @CRLF
$strMsg &= "WinDescription: " & $oMyError.WinDescription & @CRLF
$strMsg &= "Script Line: " & $oMyError.ScriptLine & @CRLF
msgbox("","Error",  $strMsg)
SetError(1)
Endfunc

 

[water]

You could use this code to check for a valid cell reference:

#include <Excel.au3>
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookNew($oExcel)
Global $sCellToCheck = "B??"
$sResult = _Excel_ConvertFormula($oExcel, "A1", $xlA1, Default, Default, $sCellToCheck)
If @error Then MsgBox(0, "Error", "Cell reference is invalid: " & $sCellToCheck)
MsgBox(0, "", "...")
_Excel_Close($oExcel)