Barry1975

_Excel_RangeRead catch exception from CUI exe

9 posts in this topic

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

 

error.png

Share this post


Link to post
Share on other sites



Line 255 is

$oWorkbook.Save()

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

 

 

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Which version of AutoIt do you run?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

 

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

 

Share this post


Link to post
Share on other sites

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

#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

 

Share this post


Link to post
Share on other sites

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)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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