Update the Excel UDF that comes with AutoIt

StefanoVR · May 1, 2013

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 · May 1, 2013

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 · May 1, 2013

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 · May 1, 2013

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

Cybergraph · May 9, 2013

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 · May 9, 2013

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 · May 11, 2013

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

nitekram · May 18, 2013

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

_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 · May 18, 2013

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

nitekram · May 18, 2013

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 · May 18, 2013

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 · May 18, 2013

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

_Excel_BookClose($oWorkbook)

nitekram · May 18, 2013

@water

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

water · May 18, 2013

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 · May 18, 2013

@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 · May 18, 2013

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

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

nitekram · May 18, 2013

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 · May 18, 2013

I'm glad it was that easy

nitekram · May 19, 2013

@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

= [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 · May 19, 2013

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

Update the Excel UDF that comes with AutoIt

Recommended Posts

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

water

Myicq

Andreu

Posted Images

Ternary operator

Ternary operator

Ternary operator

Ternary operator

Ternary operator

Ternary operator

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Similar Content