Update the Excel UDF that comes with AutoIt

[water 2,629]

Which of the functions of the written by golfinhu should be implemented in the "official" Excel UDF?

_ExcelColumnLetter: Convert the column number to letter

_ExcelColumnNumber: Convert the column letter to number

_ExcelFormatR1C1ToA1: Convert R1C1 format to A1 format

_ExcelFormatA1ToR1C1: Convert A1 format to R1C1 format

_ExcelReadSheetToArray: Create a 2D array from the rows/columns of the active worksheet (*) Already went into production as of Beta 3.3.9.5 per BugTrack #2219

_ExcelPasteSpecial: Send the clipboard data to a specified range

_ExcelFormatPainter: Copy the formatting of a range to another range

_ExcelWriteSheetFromArray: Writes a 2D array to the active worksheet (*)

_ExcelWriteArray: Write an array to a row or column on the active worksheet of the specified Excel object (*)

(*) This are faster versions of already existing functions

Edited October 1, 2012 by water

[GMK 25]

If your scripts work with Excel 2000 now they should work with the brushed up version of the UDF as well.

Backward compatibility is one of the main goals.

Well, some of what works on Excel 2000, I do in COM and not use a UDF. One of the things in particular that I'd like to use are the sort functions, which are very different from older versions to newer versions. Version checking will most likely be mandatory if we're to implement such a function in the revised UDF. Edited October 1, 2012 by GMK

[GMK 25]

Which of the functions of the written by golfinhu should be implemented in the "official" Excel UDF?

_ExcelColumnLetter: Convert the column number to letter

_ExcelColumnNumber: Convert the column letter to number

_ExcelFormatR1C1ToA1: Convert R1C1 format to A1 format

_ExcelFormatA1ToR1C1: Convert A1 format to R1C1 format

_ExcelReadSheetToArray: Create a 2D array from the rows/columns of the active worksheet (*)

_ExcelPasteSpecial: Send the clipboard data to a specified range

_ExcelFormatPainter: Copy the formatting of a range to another range

_ExcelWriteSheetFromArray: Writes a 2D array to the active worksheet (*)

_ExcelWriteArray: Write an array to a row or column on the active worksheet of the specified Excel object (*)

(*) This are faster versions of already existing functions

I think the faster (fastest possible) versions of existing functions should be implemented. _ExcelPasteSpecial and _ExcelFormatPainter could also come in handy. I'm not sure the column and row formatting are necessary as core functions, but may come in handy as undocumented internal functions.

[water 2,629]

Functions we now have in the Excel UDF will work the same way after the brush up. This means that only new optional parameters will be added or existing parameters will get additional values.

For new functions I will try to make them as compatible with older versions as possible. At least Office 2003 and later will be supported.

I have Office 2002 to test here so new functions will work for this version as well.

I will post a new version of the UDF on page 1 as soon as testing is sensible. You could then give it a try with Office 2000.

[water 2,629]

I'm not sure the column and row formatting are necessary as core functions, but may come in handy as undocumented internal functions.

This are functions called internally by some of the listed functions.

If anyone else wants to see this new functions, please vote.

[Spiff59 55]

Which of the functions of the written by golfinhu should be implemented in the "official" Excel UDF?

_ExcelReadSheetToArray: Create a 2D array from the rows/columns of the active worksheet (*)

That one already went into production as of Beta 3.3.9.5 per BugTrack #2219.

[guinness 1,536]

That one already went into production as of Beta 3.3.9.5 per BugTrack #2219.

Yeh, I was going to mention that I committed that change already.

[water 2,629]

That one already went into production as of Beta 3.3.9.5 per BugTrack #2219.

Thanks a lot for this information. Mentioned on all relevant places of this thread.

[water 2,629]

Spiff59,

which of the two possibilities I have listed would you like to see implemented?

[Spiff59 55]

I've come to agree that the FileOpen()/FileClose() test is inappropriate.

The assumption ought to be that in most cases the spreadsheet will be available for update and finding it already in a locked state will be exceptional. So, implementing additional code that is much faster in the rare cases, but slower in normal cases, no longer seems a good idea to me. The $oExcel.ActiveWorkBook.Readonly test seems the correct way to identify a failed open-for-write call.

That leaves us to decide how to treat the result. Treating a request (for an updatable spreadsheet) that can not be accomodated as an error seems the proper route to me. I guess we'd have to weigh in the likelyhood of script-breaking, or the effort required to fix a broken script, and decide whether a "patch" is justified over a "fix".

What do others think?

[water 2,629]

Let's assume we close the read-only copy of the workbook and return an error.

This will break all scripts which just do read-only operations but didn't set the $fReadOnly flag to True.

All scripts modifying the workbook would crash at the latest when saving the workbook.

I think we should return an error and close the read-only copy. As there is a flag to define the access mode the function should only do what it is told to do. If the function can't do it an error schould be returned. Even when this is script breaking.

I would be glad to hear what others think!

Edited October 1, 2012 by water

[Spiff59 55]

You're right, since the function defaults to $fReadOnly = False, and it's an optional parameter, this could break a lot more scripts than I was thinking. Were this day one of the function, I might vote for $fReadOnly = True being the optional default parameter, and making users specifically request write access, then returning an error when it isn't given. But since this function has been "in the wild" for a long time, now I'm wavering on what to do.

Am betting (or hoping) others will pipe in to help form a consensus.

Edited October 1, 2012 by Spiff59

[Spiff59 55]

Neither the production UDF or golfinhu's ExcelEX UDF have any sort of search function.

I wrote this when bored to help out someone in a forum thread here. I wonder if it might be a worthy addition?

expandcollapse popup

#include <Array.au3>

$Workbook = @ScriptDir & "test.xls"

;===================================================================================================================================
; example calling function when workbook is not open, passing workbook as a path/filename
;===================================================================================================================================
; Find "1939" in Column C, exact match, case insensitive, return matching column data and data of column to left of match
$aResult = _ExcelFindCells($Workbook, "1939", "C:C", True, False, "0,0|0,-1")
_ArrayDisplay($aResult, "search using filename")

;===================================================================================================================================
; example calling function (repeatedly) when workbook is already open, passing workbook as an object
;===================================================================================================================================
$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 0
$oExcel.WorkBooks.Open($Workbook)

; Find "The" in workbook, partial match, case sensitive, return matching column data
$aResult = _ExcelFindCells($oExcel, "The", "*", False, True, "0,0")
_ArrayDisplay($aResult, "search using object")

; Find "20" between A10 and B25, partial match, case insensitive, return matching column data
$aResult = _ExcelFindCells($oExcel, "20", "A10:D25", False, False, "0,0")
_ArrayDisplay($aResult, "search using object")

; Find "* Story" in workbook, exact match (with wildcard), case sensitive, return matching column data and 2 columns to the right
$aResult = _ExcelFindCells($oExcel, "* Story", "*", True, True, "0,0|0,2")
_ArrayDisplay($aResult, "search using object")

; Find "1959" in workbook, exact match, case sensitive, return no column data
$aResult = _ExcelFindCells($Workbook, "1959", "*", True)
_ArrayDisplay($aResult, "search using object")

$oExcel.Quit
$oExcel = ""
Exit

; #FUNCTION# =======================================================================================================================
; Name...........: _ExcelFindCells
; Description ...: Searches an Excel workbook returning the addresses of cells matching the search string, and optionally the
;    context of any cells in proximity to each matched cell as specified by the $sData parameter.
; Syntax.........: _ExcelFindCells($sFilePath, $sSearch [, $sRange] [, $bFull] [, $bCase] [,$sData])
; Parameters ....: $sFilePath - Object to an open workbook, or, full path/filename of an unopened workbook
;                $sSearch - Search string (* wildcard allowed - only effective when $bFull = True)
;                $sRange    - Range of cells to search. Format = "A1:D99". Default = "*"
;                $bFull  - False = allow partial match (default), True = entire cell contents must match exactly
;                $bCase  - False = case insensitive (default), True = case sensitive
;                $sData  - Cell(s) from which to return cell values. Specified as "row,col" offsets from the matching cell,
;       delimited by "|". Default = "" (do not return cell contents)
;                            Example: "0,0|0,1" will return the values of the matched cell and one cell to the right.
; Return values .: Success  - Returns a 2-dimension, 1-based array containing matching cell addresses in column 0, and
;        a variable number of columns containing cell contents (per $sData).
;       Element [0][0] contains number of rows returned. Element [0][1] contains number of data columns requested
;                @error=1 - Unable to start Excel application
;                @error=2 - $sFilePath does not exist
; Author ........: Spiff59
; ==================================================================================================================================
Func _ExcelFindCells($sFilePath, $sSearch = "*", $sRange = "*", $bFull = False, $bCase = False, $sData = "")
    If IsObj($sFilePath) Then
        Local $oExcel = $sFilePath
    Else
        If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
        Local $oExcel = ObjCreate("Excel.Application") ; if called with filename, start excel
        If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
        $oExcel.Visible = 0
        $oExcel.WorkBooks.Open($sFilePath)
    EndIf
    Local $aData[1] = [0], $sBEL = Chr(7), $sBEL2 = $sBEL & $sBEL
    Local $sAddressList, $sValueList, $aTemp
    If $bFull <> True Then $bFull = False
    If $bCase <> True Then $bCase = False
    If $sData Then $aData = StringSplit($sData, "|") ; offsets of cell data to return
    If $sRange = "*" Then
;       $oRange = $oExcel.ActiveSheet.UsedRange
        $oRange = $oExcel.Cells
    Else
        $oRange = $oExcel.Range($sRange)
    EndIf
    $oMatch = $oRange.Find($sSearch, Default, Default, $bFull, Default, Default, $bCase)
    If Isobj($oMatch) Then
        $oFirst = $oMatch.Address
        While IsObj($oMatch)
            $sAddressList &= StringReplace($oMatch.Address, "$", "") & $sBEL ; save address of match
            If $aData[0] Then ; cell data requested
                For $x = 1 to $aData[0]
                   $aTemp = StringSplit($aData[$x], ",")
                    $sValueList &= $oMatch.Offset($aTemp[1], $aTemp[2]).Value & $sBEL
                Next
                $sValueList &= $sBEL
            EndIf
            $oMatch = $oRange.Findnext($oMatch)
            If $oMatch.Address = $oFirst then ExitLoop
        WEnd
        $oFirst = ""
    EndIf
    $oMatch = ""
    $oRange = ""
    If Not IsObj($sFilePath) Then $oExcel.Quit ; if called with filename, shutdown excel
    $oExcel = ""

    Local $iCols = $aData[0] + 1 + ($aData[0] = 0)
    If Not $sAddressList Then ; no matches, return array with 0 count
        Local $sReturn[1][$iCols] = [[0,$aData[0]]] ;format empty output array
     Return $sReturn
    EndIf

    $sAddressList = StringSplit(StringTrimRight($sAddressList, 1), $sBEL) ; create array of matching cell addresses
    Local $sReturn[$sAddressList[0] + 1][$iCols] = [[$sAddressList[0], $aData[0]]] ; format output array
    $sValueList = StringSplit(StringTrimRight($sValueList, 2), $sBEL2, 1) ; create array of requested cell values
    For $x = 1 to $sReturn[0][0]
        $sReturn[$x][0] = $sAddressList[$x] ; matching cell address
        If $aData[0] Then ; cell data requested
           $aTemp = StringSplit($sValueList[$x], $sBEL)
         For $y = 1 to $aTemp[0] ; requested cell data
                $sReturn[$x][$y] = $aTemp[$y]
         Next
        EndIf
    Next
    Return $sReturn
EndFunc

I sometimes assume if you're bothering to write code you ought to at least be able to send a function valid parameters, so it may be that some additional parameter (bounds) checking or error handling would be a good idea.

Edit: added test.xls attachment

test.xls

Edited October 4, 2012 by Spiff59

[water 2,629]

Hi Spiff59,

that's a very good idea - will add it to the ToDo list right now.

I hope to post the first alpha of the enhanced UDF for you to play with in the next few days.

[water 2,629]

I have added the first Alpha version of the new Excel UDF to post #1 for you to play with.

Any feedback is needed and welcome!

[water 2,629]

Backward compatibility is one of the main goals when enhancing the Excel UDF. But there are some issues with the design of some functions.

I would like to discuss this issues and get as much feedback as possible before I decide what to do.

--------------------------------------------------------------------------------------------------------------------------

_ExcelBookOpen and _ExcelBookNew start a new instance of Excel for every workbook

That's a waste of ressources.

The way it works now it's not easy to get a list of open workbooks (opened by the UDF or the user)

Suggestion: I would like to change this functions so they attach to an existing instance of Excel

--------------------------------------------------------------------------------------------------------------------------

_ExcelBookOpen opens an already open workbook as readonly even when the user wants write access (parameter $fReadOnly = False)

_ExcelBookOpen now doesn't return an error but returns an object to a read-only copy of the spreadsheet. This could crash the script when you modify the read-only copy or try to save the workbook.

Possible solutions:

1) Still return the read-only copy and set @error to 0 but set @extended to 1 so the user can decide what to do. This will have the least impact on existing scripts

2) Return an error. The user has to set parameter $fReadOnly to True to get a read-only copy of the workbook. Could be script breaking.

Suggestion: Return an error. It's a clean solution and will only break a few scripts

Please tell me what you think and which solution I should implement!

[Spiff59 55]

I believe at work my Excel.au3 already has a modified _ExcelBookOpen() function in it.

I made the changes mentioned here to the function:

And also applied the logic talked about in the very next post in that thread...

I changed the start of the function to behave like the _ExcelFindCell function. Where if it was passed a filename, it would start and then stop an excel process, but if passed an object, it would use the existing object and not start and stop excel. I occasionally run a batch process that updates as many as 20,000 spreadsheets, and found ExcelBookOpen() unusable in it's current form (the job would run for hours!).

[BrewManNH 1,315]

Why would you have excelbookopen attach to an opened file? You would attach to one that's open using the function designed for it, ExcelBookAttach, and open a file using ExcelBookOpen. If someone is using the wrong function, why would you aid them in doing that?

[Spiff59 55]

I;m not suggesting attaching to an open file, but using an already running instance of Excel.

If you're processing a large number of Excel files, _ExcelBookOpen is dog-slow because it starts and stops Excel for every file processed. I'm not sure what I changed on my copy of the function at work, I don't have access to it presently. Or, it may be I opted to not use the UDF at all in multiple-file scripts. But I've always considered the inability to have a loop that calls _ExcelBookOpen, does some processing, and then calls _ExcelBookSave, without constantly starting and stopping the Excel process a big drawback to the UDF.

[water 2,629]

The functions will be changed this way: Check if an instance of Excel is already running. If true, don't start a new instance.

$oExcel = ObjGet("", "Excel.Application")
if @error Then $oExcel = ObjCreate("Excel.Application")

This saves ressources (processing time, memory) and allows to access all workbooks at once. Edited October 7, 2012 by water