Update the Excel UDF that comes with AutoIt

[StefanoVR]

Good evening Water. I need your help. I' have not a big experience using autoit . In my scripts I have experienced the limitations of the standard Excel UDF so I tried to use our one. I heve never added a UDF to autoit so i followed the instructions athttp://www.autoitscript.com/wiki/Adding_UDFs_to_AutoIt_and_SciTE but I don't think to have done mistakes but something should be wrong...

When I start the script with your functions I receive a lot of errors about constants..

And I really can't understand where the problem is.

Probably is a newbie error.. could you give me any suggestion?

Thanks in advance and sorry for bothering you.. Stefano

>19:20:41 Starting AutoIt3Wrapper v.2.1.0.33 Environment(Language:0410 Keyboard:00000410 OS:WIN_7/Service Pack 1 CPU:X64 OS:X64)

>Running AU3Check (3.3.9.4) from:C:Program Files (x86)AutoIt3beta

"C:UDFExcel Rewrite.au3"(23,40) : error: $xlCenter previously declared as a 'Const'.

Global Const $xlCenter = -4108 ; Center

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

"C:UDFExcel Rewrite.au3"(24,36) : error: $xlLeft previously declared as a 'Const'.

Global Const $xlLeft = -4131 ; Left

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

"C:UDFExcel Rewrite.au3"(25,38) : error: $xlRight previously declared as a 'Const'.

Global Const $xlRight = -4152 ; Right

[water]

This message tells you that the constants have already been defined elsewhere.

Do you include "Excel.au3" and "Excel Rewrite.au3" in your script?

[StefanoVR]

Yes because I use some functions of "excel.Au3" in the script.

Could be useful use both otherwise, probably, I should rewrite a lot of lines

Edited May 1, 2013 by StefanoVR

[water]

You can't combine both UDF's, sorry. My version is designed as a replacement for Excel.au3.

[Cybergraph]

Right today I had the same problem. Fortunately the "old" Excel function I needed to use was _ExcelSheetMove. I roughly made a copy and paste of it inside Excel Rewrite (adding also the _ExcelSheetList because called by the other function) and worked very well.

You have to make some attempts, analyze the code of functions you need and see what happens.

Good luck!

[water]

The ExcelEX UDF will have all/most of the functions you now have in the Excel UDF with enhanced functionality plus a few new functions.

Hope to release a new alpha version in the next days.

[water]

Released a new Alpha version. For download and a history of changes please see post #1.

[nitekram]

I have downloaded the latest version: Alpha4? (think that is the latest version)

 

I can verify that the range is now working with the function _Excel_RangeRead() -thanks

 

There are some issues I am seeing:

 

1) _Excel_BookAttach() the ObjGet() is being called with too many parameters, took this out, and seemed to be OK

$oWorkbook = ObjGet("", $sCLSID_Workbook, $iCount + 1) -->> $oWorkbook = ObjGet("", $sCLSID_Workbook)

 

and after that, when I close the _arraydisplay() I get this:

 

AutoIt COM error handle

Error encountered in waters reproducer script.au3:

  @AutoItVersion = 3.3.8.1

  @AutoItX64 = 0

  @Compiled = 0

  @OSArch = X86

  @OSVersion = WIN_XP

  Scriptline = 215

  NumberHex = 80020006

  Number = -2147352570

  WinDescription = Unknown name.

  Description =

  Source =

  HelpFile =

  HelpContext = 0

  LastDllError = 0

Script waters reproducer script.au3:

  @AutoItVersion = 3.3.8.1

  @AutoItX64 = 0

  @Compiled = 0

  @OSArch = X86

  @OSVersion = WIN_XP

+>11:55:59 AutoIT3.exe ended.rc:0

>Exit code: 0    Time: 118.707

 

 

 

expandcollapse popup

_COMError_Notify(2)

Func _COMError_Notify($iDebug, $sDebugFile = Default)

    Static Local $avDebugState[3] = [0, "", 0] ; Debugstate, Debugfile and AutoIt.Error object
    If $sDebugFile = Default Or $sDebugFile = "" Then $sDebugFile = @ScriptDir & "\COMError_Debug.txt"
    If Not IsInt($iDebug) Or $iDebug < -1 Or $iDebug > 3 Then Return SetError(1, 0, 0)
    Switch $iDebug
        Case -1
            Return $avDebugState
        Case 0
            $avDebugState[0] = 0
            $avDebugState[1] = ""
            $avDebugState[2] = 0
        Case Else
            If $iDebug = 2 And $sDebugFile = "" Then Return SetError(4, 0, 0)
            ; A COM error handler will be initialized only if one does not exist
            If ObjEvent("AutoIt.Error") = "" Then
                $avDebugState[2] = ObjEvent("AutoIt.Error", "__COMError_Handler") ; Creates a custom error handler
                If @error <> 0 Then Return SetError(2, @error, 0)
                $avDebugState[0] = $iDebug
                $avDebugState[1] = $sDebugFile
                Return SetError(0, 1, 1)
            ElseIf ObjEvent("AutoIt.Error") = "__COMError_Handler" Then
                Return SetError(0, 0, 1) ; COM error handler already set by a previous call to this function
            Else
                Return SetError(3, 0, 0) ; COM error handler already set to another function
            EndIf
    EndSwitch
    Return

EndFunc   ;==>_COMError_Notify

Func __COMError_Handler($oCOMError)

    Local $sTitle = "AutoIt COM error handler"
    Local $avDebugState = _COMError_Notify(-1)
    Local $sError = "Error encountered in " & @ScriptName & ":" & @CRLF & _
            "  @AutoItVersion = " & @AutoItVersion & @CRLF & _
            "  @AutoItX64 = " & @AutoItX64 & @CRLF & _
            "  @Compiled = " & @Compiled & @CRLF & _
            "  @OSArch = " & @OSArch & @CRLF & _
            "  @OSVersion = " & @OSVersion & @CRLF & _
            "  Scriptline = " & $oCOMError.Scriptline & @CRLF & _
            "  NumberHex = " & Hex($oCOMError.Number, 8) & @CRLF & _
            "  Number = " & $oCOMError.Number & @CRLF & _
            "  WinDescription = " & StringStripWS($oCOMError.WinDescription, 2) & @CRLF & _
            "  Description = " & StringStripWS($oCOMError.Description, 2) & @CRLF & _
            "  Source = " & $oCOMError.Source & @CRLF & _
            "  HelpFile = " & $oCOMError.HelpFile & @CRLF & _
            "  HelpContext = " & $oCOMError.HelpContext & @CRLF & _
            "  LastDllError = " & $oCOMError.LastDllError & @CRLF
    Switch $avDebugState[0]
        Case 1
            MsgBox(64, $sTitle & '1', $sError)
            ConsoleWrite($sTitle & " - " & $sError & @CRLF)
            FileWrite(@ScriptDir & '\error_1.txt', @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & $sTitle & @CRLF & $sError)
        Case 2
            MsgBox(64, $sTitle & '2', $sError)
            ConsoleWrite($sTitle & @CRLF & $sError)
            FileWrite(@ScriptDir & '\error_2.txt', @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & $sTitle & @CRLF & $sError)
        Case 3
            MsgBox(64, $sTitle & '3', $sError)
            FileWrite($avDebugState[1], @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & $sTitle & _
                    " - " & $sError & @CRLF)
            FileWrite(@ScriptDir & '\error_3.txt', @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & $sTitle & @CRLF & $sError)

    EndSwitch

EndFunc   ;==>__COMError_Handler


#include "Excel Rewrite.au3"
#include <array.au3>

Const $sPath = 'c:\'

Local $sItems = "Script " & @ScriptName & ":" & @CRLF & _
            "  @AutoItVersion = " & @AutoItVersion & @CRLF & _
            "  @AutoItX64 = " & @AutoItX64 & @CRLF & _
            "  @Compiled = " & @Compiled & @CRLF & _
            "  @OSArch = " & @OSArch & @CRLF & _
            "  @OSVersion = " & @OSVersion & @CRLF




$ExcelFile = FileOpenDialog('Look GCAR report spreadsheet', $sPath, "Excel (*.xls;*.xlsx)", 1 + 2)
Global $oExcel = _Excel_Open(False, Default, Default, True) ; _Excel_Open(False)
; _Excel_Open(False, Default, Default, Default, Default, True)
If @error <> 0 Then Exit MsgBox(16, "Excel UDF: _Excel_Open " & $ExcelFile, "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpen($oExcel, $ExcelFile, True)
If @error Then
    MsgBox(16, "Excel UDF: _Excel_BookOpen " & $ExcelFile, "Error opening '" & $ExcelFile & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Else
    $aArrayExcel = _Excel_RangeRead($oExcel, $oWorkbook, Default, "A7:CC1200")
    If @error Then MsgBox(16, "Excel UDF: _Excel_RangeRead " & $ExcelFile, "Error reading '" & $ExcelFile & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
EndIf

_ArrayDisplay($aArrayExcel)

_Excel_BookClose($oExcel, $oWorkbook)
_Excel_Close($oExcel)

ConsoleWrite($sItems)

edit, added clarity to _Excel_RangeRead Edited May 18, 2013 by nitekram

[guinness]

It says in that error you're using v3.3.8.1 and not the beta v3.3.9.4.

[nitekram]

Sorry about that...I am using his UDF though (in the same directory as the his tester script), does that matter?

I know that the first error reported would not matter if I was using beta or not as it is a counter.

[water]

The next version of the UDF will support AutoIt 3.3.8.1 as well. Functionality of _Excel_BookAttach will be reduced but you will not get an error.

[water]

_Excel_BookClose no longer needs the Excel application object as parameter 1. Should be

_Excel_BookClose($oWorkbook)

[nitekram]

@water

That fixed the error on closing - thanks for all your hard work on revamping, and making this UDF run faster then ever.

[water]

There are more things to come.  Stay tuned!

To all users:

Every new Alpha version I release may come with some/a lot of script breaking changes. Please read the History file to see what has changed and how your existing scripts need to be modified!

[nitekram]

@water

Having similar issues, and not sure how to proceed: I am trying to keep my preexisting excel file up and visible, as it is now, it make it invisible and the process is still running: maybe my parameters are wrong?

Here are my steps

_Excel_Open(False, Default, False, True, True)

_Excel_BookOpen($oOExcel, $ExcelFile, True, False)

_Excel_RangeRead($oOExcel, $oWorkbook, Default, Default)

_Excel_BookClose($oWorkbook, False)

have removed _excel_close and makes no difference

_Excel_Close($oOExcel)

The good news, is it is no longer saving a copy to my documents folder (YES!!!, thanks)

edit, forgot to close a )

Edited May 18, 2013 by nitekram

[water]

If you start a new instance the already running instance should be unaffected:

$oOExcel = _Excel_Open(False, Default, False, True, True, True)

[nitekram]

I am just that stupid, I call your functions twice, for each excel doc I have. I forgot to make the change in my second one - FORGIVE ME

And thanks again for your help...

edit and never checked the first one again, to see if there was a difference

Edited May 18, 2013 by nitekram

[water]

I'm glad it was that easy

[nitekram]

@water

I have no idea why this showed up today, and seems to have broken my script, but yesterday - it seemed to work fine.???

Here is the crash, without error handling:

P:AutoitfilesExcel Rewrite.au3 (102) : ==> The requested action with this object has failed.:
$oExcel.Calculation = $iCalculation
$oExcel.Calculation = $iCalculation^ ERROR

 

Here is the error:

AutoIt COM error handler
Error encountered in waters reproducer script.au3:
  @AutoItVersion = 3.3.8.1
  @AutoItX64 = 0
  @Compiled = 0
  @OSArch = X86
  @OSVersion = WIN_XP
  Scriptline = 102
  NumberHex = 80020009
  Number = -2147352567
  WinDescription =
  Description = Unable to set the Calculation property of the Application class
  Source = Microsoft Excel
  HelpFile = xlmain11.chm
  HelpContext = 0
  LastDllError = 0
Script waters reproducer script.au3:
  @AutoItVersion = 3.3.8.1
  @AutoItX64 = 0
  @Compiled = 0
  @OSArch = X86
  @OSVersion = WIN_XP

Calling with your parameters: What am I doing wrong? If I let the error hit, it still opens and seems to read it as per my _ArrayDisplay(), but crashes if I take out the __COMError_Handler

 

$oOExcel = _Excel_Open(False, Default, False, True, True, True)

 

I am trying to troubleshoot, by taking out that line (;$oExcel.Calculation = $iCalculation) - and it seems to work? Got any ideas

 

With your code below

expandcollapse popup

= [0, "", 0] ; Debugstate, Debugfile and AutoIt.Error object
    If $sDebugFile = Default Or $sDebugFile = "" Then $sDebugFile = @ScriptDir & "\COMError_Debug.txt"
    If Not IsInt($iDebug) Or $iDebug < -1 Or $iDebug > 3 Then Return SetError(1, 0, 0)
    Switch $iDebug
        Case -1
            Return $avDebugState
        Case 0
            $avDebugState[0] = 0
            $avDebugState[1] = ""
            $avDebugState[2] = 0
        Case Else
            If $iDebug = 2 And $sDebugFile = "" Then Return SetError(4, 0, 0)
            ; A COM error handler will be initialized only if one does not exist
            If ObjEvent("AutoIt.Error") = "" Then
                $avDebugState[2] = ObjEvent("AutoIt.Error", "__COMError_Handler") ; Creates a custom error handler
                If @error <> 0 Then Return SetError(2, @error, 0)
                $avDebugState[0] = $iDebug
                $avDebugState[1] = $sDebugFile
                Return SetError(0, 1, 1)
            ElseIf ObjEvent("AutoIt.Error") = "__COMError_Handler" Then
                Return SetError(0, 0, 1) ; COM error handler already set by a previous call to this function
            Else
                Return SetError(3, 0, 0) ; COM error handler already set to another function
            EndIf
    EndSwitch
    Return

EndFunc   ;==>_COMError_Notify

Func __COMError_Handler($oCOMError)

    Local $sTitle = "AutoIt COM error handler"
    Local $avDebugState = _COMError_Notify(-1)
    Local $sError = "Error encountered in " & @ScriptName & ":" & @CRLF & _
            "  @AutoItVersion = " & @AutoItVersion & @CRLF & _
            "  @AutoItX64 = " & @AutoItX64 & @CRLF & _
            "  @Compiled = " & @Compiled & @CRLF & _
            "  @OSArch = " & @OSArch & @CRLF & _
            "  @OSVersion = " & @OSVersion & @CRLF & _
            "  Scriptline = " & $oCOMError.Scriptline & @CRLF & _
            "  NumberHex = " & Hex($oCOMError.Number, 8) & @CRLF & _
            "  Number = " & $oCOMError.Number & @CRLF & _
            "  WinDescription = " & StringStripWS($oCOMError.WinDescription, 2) & @CRLF & _
            "  Description = " & StringStripWS($oCOMError.Description, 2) & @CRLF & _
            "  Source = " & $oCOMError.Source & @CRLF & _
            "  HelpFile = " & $oCOMError.HelpFile & @CRLF & _
            "  HelpContext = " & $oCOMError.HelpContext & @CRLF & _
            "  LastDllError = " & $oCOMError.LastDllError & @CRLF
    Switch $avDebugState[0]
        Case 1
            MsgBox(64, $sTitle & '1', $sError)
            ConsoleWrite($sTitle & " - " & $sError & @CRLF)
            FileWrite(@ScriptDir & '\error_1.txt', @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & $sTitle & @CRLF & $sError)
        Case 2
            MsgBox(64, $sTitle & '2', $sError)
            ConsoleWrite($sTitle & @CRLF & $sError)
            FileWrite(@ScriptDir & '\error_2.txt', @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & $sTitle & @CRLF & $sError)
        Case 3
            MsgBox(64, $sTitle & '3', $sError)
            FileWrite($avDebugState[1], @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & $sTitle & _
                    " - " & $sError & @CRLF)
            FileWrite(@ScriptDir & '\error_3.txt', @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & $sTitle & @CRLF & $sError)

    EndSwitch

EndFunc   ;==>__COMError_Handler


#include "Excel Rewrite.au3"
#include <array.au3>

Const $sPath = 'c:\'

Local $sItems = "Script " & @ScriptName & ":" & @CRLF & _
            "  @AutoItVersion = " & @AutoItVersion & @CRLF & _
            "  @AutoItX64 = " & @AutoItX64 & @CRLF & _
            "  @Compiled = " & @Compiled & @CRLF & _
            "  @OSArch = " & @OSArch & @CRLF & _
            "  @OSVersion = " & @OSVersion & @CRLF

$ExcelFile = FileOpenDialog('Look GCAR report spreadsheet', $sPath, "Excel (*.xls;*.xlsx)", 1 + 2)
Global $oExcel = _Excel_Open(False, Default, False, True, True, True) ;_Excel_Open(False, Default, Default, True, False, True) ;_Excel_Open(False, Default, Default, True) ; _Excel_Open(False)
; _Excel_Open(False, Default, Default, Default, Default, True)
If @error <> 0 Then Exit MsgBox(16, "Excel UDF: _Excel_Open " & $ExcelFile, "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpen($oExcel, $ExcelFile, True)
If @error Then
    MsgBox(16, "Excel UDF: _Excel_BookOpen " & $ExcelFile, "Error opening '" & $ExcelFile & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Else
    Local $iMyRange = "A7:CC1200"
    $aArrayExcel = _Excel_RangeRead($oExcel, $oWorkbook, Default, Default)
    If @error Then MsgBox(16, "Excel UDF: _Excel_RangeRead " & $ExcelFile, "Error reading '" & $ExcelFile & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
EndIf

_ArrayDisplay($aArrayExcel)

;_Excel_BookClose($oExcel, $oWorkbook)
_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)

ConsoleWrite($sItems)

Edited May 19, 2013 by nitekram

[water]

The rewrite is made to be run with one of the 3.3.9.x beta versions.