Update the Excel UDF that comes with AutoIt

[Andreu]

If this is the route you want to go with it, I actually have a bunch of examples (such as the one I provided). 

Very familiar with auto-formatting my reports via VB in Excel, and at work I have a few modules that I've developed over time that do everything rather efficiently. When I go in tomorrow, I'll translate them over to AutoIt and share to see if they spark any ideas as to how you'd like to go about it.

May tweak them to better suit "mass" use... as these, self coded, I personally know how to use them so their not quite idiot proof lol.

In regard to whether or not you should add them? Yes, but at the same time no. I think it's easy enough to use the com yourself using appropriate With... Endwith, since you can globalize your obj and wb variables, whereas in the UDF that probably wouldn't go over as well. (So basically the same logic as to why you will not include Macro functionality.)

Yes, however, because being done properly... it could be a big time saver. Whats the possibility of including the parameters in current functions? Write to a cell and format it simultaneously. Everything the formatting functions would need is already presented in the current params. I can't write now, but I want to code out some examples and try them myself. This is all word vomit...

[water]

I think it is sensible to add formatting functions if the function does more than just take a parameter and set the cell property.

Word has this _WordmacroRun function:

expandcollapse popup

; #FUNCTION# ====================================================================================================================
; Name...........: _WordMacroRun
; Description ...: Runs a Visual Basic macro
; Parameters ....: $o_object            - Object variable of a Word.Application object
;                  $s_MacroName     - The name of the macro. Can be any combination of template,
;                                           module, and macro name. (See Remarks)
;                  $v_Arg1              - Optional: The first parameter to pass to the macro
;                   $v_Argn         - ...
;                  $v_Arg30         - Optional: The thirtieth parameter to pass to the macro
; Return values .: On Success   - Returns 1
;                  On Failure   - Returns 0 and sets @ERROR
;                   @ERROR      - 0 ($_WordStatus_Success) = No Error
;                               - 1 ($_WordStatus_GeneralError) = General Error
;                               - 2 ($_WordStatus_ComError) = Com Error
;                               - 3 ($_WordStatus_InvalidDataType) = Invalid Data Type
;                               - 4 ($_WordStatus_InvalidObjectType) = Invalid Object Type
;                   @Extended   - Contains invalid parameter number
; Author ........: Bob Anthony
; Remarks .......: If you specify the document name, your code can only run macros in documents
;                  related to the current context — not just any macro in any document.
; ===============================================================================================================================
Func _WordMacroRun(ByRef $o_object, $s_MacroName, $v_Arg1 = Default, $v_Arg2 = Default, $v_Arg3 = Default, $v_Arg4 = Default, $v_Arg5 = Default, $v_Arg6 = Default, $v_Arg7 = Default, $v_Arg8 = Default, $v_Arg9 = Default, $v_Arg10 = Default, $v_Arg11 = Default, $v_Arg12 = Default, $v_Arg13 = Default, $v_Arg14 = Default, $v_Arg15 = Default, $v_Arg16 = Default, $v_Arg17 = Default, $v_Arg18 = Default, $v_Arg19 = Default, $v_Arg20 = Default, $v_Arg21 = Default, $v_Arg22 = Default, $v_Arg23 = Default, $v_Arg24 = Default, $v_Arg25 = Default, $v_Arg26 = Default, $v_Arg27 = Default, $v_Arg28 = Default, $v_Arg29 = Default, $v_Arg30 = Default)

    If Not IsObj($o_object) Then
        __WordErrorNotify("Error", "_WordMacroRun", "$_WordStatus_InvalidDataType")
        Return SetError($_WordStatus_InvalidDataType, 1, 0)
    EndIf
    ;
    If Not __WordIsObjType($o_object, "application") Then
        __WordErrorNotify("Error", "_WordMacroRun", "$_WordStatus_InvalidObjectType")
        Return SetError($_WordStatus_InvalidObjectType, 1, 0)
    EndIf
    ;
    Local $s_ErrorMSG, $i_Extended, $i_ErrorStatusCode = $_WordStatus_Success

    ; Setup internal error handler to Trap COM errors, turn off error notification
    Local $status = __WordInternalErrorHandlerRegister()
    If Not $status Then __WordErrorNotify("Warning", "_WordMacroRun", _
            "Cannot register internal error handler, cannot trap COM errors", _
            "Use _WordErrorHandlerRegister() to register a user error handler")
    Local $f_NotifyStatus = _WordErrorNotify() ; save current error notify status
    _WordErrorNotify(False)

    $o_object.Run($s_MacroName, $v_Arg1, $v_Arg2, $v_Arg3, $v_Arg4, $v_Arg5, _
            $v_Arg6, $v_Arg7, $v_Arg8, $v_Arg9, $v_Arg10, _
            $v_Arg11, $v_Arg12, $v_Arg13, $v_Arg14, $v_Arg15, _
            $v_Arg16, $v_Arg17, $v_Arg18, $v_Arg19, $v_Arg20, _
            $v_Arg21, $v_Arg22, $v_Arg23, $v_Arg24, $v_Arg25, _
            $v_Arg26, $v_Arg27, $v_Arg28, $v_Arg29, $v_Arg30)

    If @error = $_WordStatus_ComError Then
        If $WordComErrorNumber = -2147352567 Then
            $i_ErrorStatusCode = $_WordStatus_InvalidValue
            Switch $WordComErrorWinDescription
                Case "Member not found."
                    $s_ErrorMSG = "The specified macro does not exist."
                    $i_Extended = 2
                Case "Invalid number of parameters."
                    $s_ErrorMSG = "Invalid number of parameters."
                    $i_Extended = -1
            EndSwitch
        Else
            $i_ErrorStatusCode = $_WordStatus_ComError
        EndIf
    EndIf

    ; restore error notify and error handler status
    _WordErrorNotify($f_NotifyStatus) ; restore notification status
    __WordInternalErrorHandlerDeRegister()

    Switch $i_ErrorStatusCode
        Case $_WordStatus_Success
            Return SetError($_WordStatus_Success, 0, 1)
        Case $_WordStatus_InvalidValue
            __WordErrorNotify("Error", "_WordMacroRun", "$_WordStatus_InvalidValue", $s_ErrorMSG)
            Return SetError($_WordStatus_InvalidValue, $i_Extended, 0)
        Case $_WordStatus_ComError
            __WordErrorNotify("Error", "_WordMacroRun", "$_WordStatus_ComError", "There was an error while executing the 'Run' Method.")
            Return SetError($_WordStatus_ComError, 0, 0)
        Case Else
            __WordErrorNotify("Error", "_WordMacroRun", "$_WordStatus_GeneralError", "Invalid Error Status - Notify Word.au3 developer.")
            Return SetError($_WordStatus_GeneralError, 0, 0)
    EndSwitch
EndFunc   ;==>_WordMacroRun

When removing all error checking code only a single line of code remains: "$o_object.Run($s_MacroName, ...)"

So this function was removed from the rewritten UDF.

At the moment I tend to not add formatting functions. More helpful functions like a copy format etc. should be added.

[JailDoctor]

The ExcelEX alpha 6 zip file is empty or corrupt.

Is there another place from where to download it?

[Andreu]

The ExcelEX alpha 6 zip file is empty or corrupt.

Is there another place from where to download it?

Just tested it, and it works fine for me.

WinRAR = the best.

[water]

The ZIP-file was created with native Windows 7. Is something blocking the download of ZIP archives?

[water]

Another question: Do you have the same problem with the Alpha 5 file?

[water]

Another poll

At the moment I have two kinds of function calls in my UDF (due to the fact that I copied the functions from all over the forum). Example:

A function needs the worksheet object to process. It should be possible to pass a default value so that the active worksheet is being used. To do this the function needs to access the application or workbook object. These are no global variables so they need to be passed as parameters. Possible solutions:

Passing 3 parameters: Application object, Workbook object and Worksheet object. The Worksheet object is set to Default to denote that the active Worksheet should be used.

Function($oApplicationObject, Default, Default, ...)

Function(Default, $oWorkbookObject, Default, ...)

Passing 1 parameter: Application object or Workbook object. If the parameter is an application or workbook object then the active worksheet will be processed.

Function($oApplicationObject, ...)

Function($oWorkbookObject, ...)

Which of the solutions would you prefer?

[Bowmore]

@water

In my opinion the function should expect to be passed one object of the correct type with no default allowed. For example if a function is going to read write or format an excel worksheet then it should be up to the user to pass a valid worksheet object to the function, getting the active worksheet object before calling the function if necessary.

Each function should check that a valid object of the correct type has been passed.

For Example

If Not IsObj($oWrkSht) Then Return SetError(1, 0, 0)
  If ObjName($oWrkSht) <> "_Worksheet" Then Return SetError(2, 0, 0)

One of the problems with the current/old Excel UDF is that it is not always clear what the object being passed refers to, which can cause problems when trying to access some Excel property of method directly using the object returned.

[water]

Thanks for your reply!

So security/reliability goes before comfort?

Other opinions?

[water]

If I have made up my mind how the parameters should be handled (see discussion above) I will brush up the existing functions so I can release a first beta of the UDF.

No script breaking changes should then be necessary for the existing functions (so help me god).

Hope this will happen in the next 2 weeks.

So don't be shy and post what you think.

[water]

@water

In my opinion the function should expect to be passed one object of the correct type with no default allowed.

I will soon post an Alpha version of the UDF with all functions modified accordingly. Hope you'll like it

[Tjalve]

Excelent! (like mr Burns)

Right now im doing alot of charts in excel and im hoping that someone could come up with some simple UDFs for simple chart manipulation. I checked your excelchart UDF Water, but it seems to complicated for the things that i would like to do. I would like to just add some data to the laste cell (witch i do today with the standard excel.au3) and then increase the "range" of an existing chart to include the new values. Then export the chart to a PNG file (or clipboard). Is there a simple way of doing this without involving macros and stuff?

[water]

Tjalve,

the easiest way to create a chart is calling function _XLChart_ChartCreate. Pass the mandatory parameters, the rest is being done by Excel. If something needs to be changed call the corresponding function.

If you have problems, I will be happy to assist.

[Tjalve]

Tjalve,

the easiest way to create a chart is calling function _XLChart_ChartCreate. Pass the mandatory parameters, the rest is being done by Excel. If something needs to be changed call the corresponding function.

If you have problems, I will be happy to assist.

I appritiate the help. Please let me know if i should move this discussion to another tread...

I dont want to create a new chart. I want to modify the existing one.

I have around 20 excel documents with charts that i regulary update with new data. So would i would like to do is:

Open the existing excel, add the new data (a new name and a new value) <- Ive completed this part. Then i need to make sure that the existing chart include my newly added values. Then i need to sort by the values column. Then export the chart as a PNG and then save the document again. Sometimes a also may need to increase the size of the chart.

Edited July 2, 2013 by Tjalve

[water]

If you like to discuss the ExcelChart UDF then please let's start a thread >here.

I think it should be possible to do what you want.

Haven't used this UDF for many months. Have to get into it again ...

[water]

Tjalve,

I started a new thread on the ExcelChart GHS >thread and posted my reply there.

[Tjalve]

Tjalve,

I started a new thread on the ExcelChart GHS >thread and posted my reply there.

Thank you

[water]

Hope my reply is useful

[water]

@water

In my opinion the function should expect to be passed one object of the correct type with no default allowed. For example if a function is going to read write or format an excel worksheet then it should be up to the user to pass a valid worksheet object to the function, getting the active worksheet object before calling the function if necessary.

Each function should check that a valid object of the correct type has been passed.

For Example

If Not IsObj($oWrkSht) Then Return SetError(1, 0, 0)
  If ObjName($oWrkSht) <> "_Worksheet" Then Return SetError(2, 0, 0)

One of the problems with the current/old Excel UDF is that it is not always clear what the object being passed refers to, which can cause problems when trying to access some Excel property of method directly using the object returned.

I modified the UDF as requested. Attached you will find the first try. Please check the first post. Please check the History.txt file for a list of (script breaking) changes.

Please tell me what you think!

Edited July 26, 2013 by water

[nitekram]

@water

I am in need of a function to 'import' into excel with delimited char (csv) - Possible?