Update the Excel UDF that comes with AutoIt

[water]

In Excel 2007 and later there is limit of 65536 rows. If you try to transpose an array with more rows the transpose method crashes.

Do you think the limit of 65536 elements is limited to Excel 2003 or is this true for Excel 2007 and 2010 too?

[water]

I just tested the maximum array you can assign to a range. For Excel 2010 the limit is 65536 (2^16) rows with 152 columns equals 9961472 cells.

Can you please check the maximum values for Excel 2003?

; Create worksheet
    $oExcel = ObjCreate("Excel.Application")
    With $oExcel
        .Visible = True
        .WorkBooks.Add()
        .ActiveWorkbook.Sheets(1).Select()
    EndWith
    ; Create array
    $rows = 2^16 ; 2^16 = 65536
    $columns = 152 ; 152 = 2^7 + 2^4 + 2^3
    Global $aArrayIn[$rows][$columns] = [[1]]
    ; Fill corners of the array
    $aArrayIn[0][0] = 1
    $aArrayIn[0][$columns-1] = $columns
    $aArrayIn[$rows-1][0] = $rows
    $aArrayIn[$rows-1][$columns-1] = $columns
    ; Transpose array
    $aArrayOut = $oExcel.WorksheetFunction.Transpose($aArrayIn)
    ; Write array to sheet
    $oExcel.Activesheet.Range($oExcel.Activesheet.Cells(1, 1), $oExcel.Activesheet.Cells($rows, $columns)).Value = $aArrayOut

[water]

I modified _Excel_RangeWrite a bit to let it use Excel internal functions to transpose the array if possible. The function now works with chunks of 65536 rows. This works for Excel 2010.

Can you please test if the following script works for Excel 2003 too?

I get the impression that we need to set the number of elements we can process in one go to 65536 cells for Excel <= 2003 and to 65536 rows for Excel >= 2007.

expandcollapse popup

#include <excel.au3>

Test_Main()

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_RangeWrite
; Description ...: Write value(s) or formula(s) to a cell or a cell range on the specified workbook and worksheet.
; Syntax.........: _Excel_RangeWrite($oExcel, $oWorkbook, $oWorksheet, $vValue, $vRangeOrRow[, $iColumn = 1[, $iArrayRowStart = 0[, $iArrayColStart = 0[, $bValue = True]]]])
; Parameters ....: $oExcel       - Excel application object
;                $oWorkbook  - Excel workbook object. If set to Default the active workbook will be used
;                $oWorksheet     - Excel worksheet object. If set to Default the active sheet will be used
;                $vValue         - Can be a string, a 1D or 2D array containing the data to be written to the worksheet
;                $vRangeOrRow - Either an A1 range (only valid when $vValue is a string) or an integer row number to write to if using R1C1
;                $iColumn    - Optional: The column to write to if using R1C1 (default = 1)
;                $iArrayRowStart - Optional: Array index base for rows (default is 0)
;                $iArrayColStart - Optional: Array index base for columns (default is 0)
;                $bValue         - Optional: If True the $vValue will be written to the value property. If False $vValue will be written to the formula property (default = True)
; Return values .: Success - Returns 1
;                Failure - Returns 0 and sets @error:
;                |1 - $oExcel is not an object
;                |2 - $oWorkbook is not an object
;                |3 - $oWorksheet is not an object
;                |4 - Parameter out of range. Sets @extended:
;                | 0 - $vRangeOrRow out of range
;                | 1 - $iColumn out of range
;                |5 - Base index out of range. Sets @extended:
;                | 0 - $iArrayRowStart out of range
;                | 1 - $iArrayColStart out of range
;                |6 - Error occurred when writing data. @extended is set to the COM error code
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike and PsaltyDS 01/04/08 - 2D version _Excel_RangeWrite(), Golfinhu (improved speed), water, GMK
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_RangeWrite($oExcel, $oWorkbook, $oWorksheet, $vValue, $vRangeOrRow, $iColumn = Default, $iArrayRowStart = Default, $iArrayColStart = Default, $bValue = Default)
If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
If $oWorkbook = Default Then $oWorkbook = $oExcel.ActiveWorkbook
If Not IsObj($oWorkbook) Then Return SetError(2, 0, 0)
If $oWorksheet = Default Then $oWorksheet = $oWorkbook.ActiveSheet
If Not IsObj($oWorksheet) Then Return SetError(3, 0, 0)
If $iColumn = Default Then $iColumn = 1
If $iColumn < 1 Then Return SetError(4, 1, 0)
If $bValue = Default Then $bValue = True
If Not IsArray($vValue) Then
If Not StringRegExp($vRangeOrRow, "[A-Z,a-z]", 0) Then
If $bValue Then
$oWorksheet.Cells($vRangeOrRow, $iColumn).Value = $vValue
Else
$oWorksheet.Cells($vRangeOrRow, $iColumn).Formula = $vValue
EndIf
Else
If $bValue Then
$oWorksheet.Range($vRangeOrRow).Value = $vValue
Else
$oWorksheet.Range($vRangeOrRow).Formula = $vValue
EndIf
EndIf
Else
If $iArrayRowStart = Default Then $iArrayRowStart = 0
If $iArrayColStart = Default Then $iArrayColStart = 0
Local $iDims = UBound($vValue, 0), $iArrayRowSize = UBound($vValue, 1), $iArrayColSize = UBound($vValue, 2)
If $iArrayColSize = 0 Then $iArrayColSize = 1
If $iArrayRowStart > $iArrayRowSize Then Return SetError(5, 0, 0)
If $iArrayColStart > $iArrayColSize Then Return SetError(5, 1, 0)
Local $iLastRow, $iLastCol
If $vRangeOrRow < 1 Then Return SetError(4, 0, 0)
Local $iNewRowArraySize = $iArrayRowSize - $iArrayRowStart
Local $iNewColArraySize = $iArrayColSize - $iArrayColStart
$iLastRow = $vRangeOrRow + $iNewRowArraySize - 1
$iLastCol = $iColumn + $iNewColArraySize - 1
Local $oRange
; #forceref $oRange
; Limits:
; <= Excel 2003: 65536 elements
; > Excel 2003: 65536 rows, number of columns depends on the content of the cells (storage used)
If UBound($vValue) > 65536 Then ; If UBound($aTransposed) * UBound($aTransposed, 2) > 65536 Then
; Create a transposed new array and add new values
Local $aTransposed[$iNewColArraySize][$iNewRowArraySize]
For $i = $iArrayRowStart To $iArrayRowSize - 1
For $j = $iArrayColStart To $iArrayColSize - 1
If $iDims = 2 Then
$aTransposed[$j - $iArrayColStart][$i - $iArrayRowStart] = $vValue[$i][$j]
Else
$aTransposed[$j - $iArrayColStart][$i - $iArrayRowStart] = $vValue[$i]
EndIf
Next
Next
Local $iTransposedRows = UBound($aTransposed)
Local $iTransposedCols = UBound($aTransposed, 2)
Local $iChunk = Int(65536 / 1) ; Int(65536 / $iTransposedRows)
Local $iStartCol = 0
Local $iEndCol = $iStartCol + $iChunk - 1
While 1
Local $aTemp[$iTransposedRows][$iEndCol - $iStartCol + 1]
For $iRow = 0 To $iTransposedRows - 1
For $iCol = 0 To $iEndCol - $iStartCol
$aTemp[$iRow][$iCol] = $aTransposed[$iRow][$iCol + $iStartCol]
Next
Next
$oRange = $oWorksheet.Range($oWorksheet.Cells($vRangeOrRow + $iStartCol, $iColumn), $oWorksheet.Cells($vRangeOrRow + $iEndCol, UBound($aTemp)))
If $bValue = 1 Then
$oRange.Value = $aTemp
Else
$oRange.Formula = $aTemp
EndIf
If @error Then Return SetError(6, @error, 0)
If $iEndCol = $iTransposedCols - 1 Then ExitLoop
$iStartCol = $iEndCol + 1
$iEndCol = $iStartCol + $iChunk - 1
If $iEndCol > $iTransposedCols - 1 Then $iEndCol = $iTransposedCols - 1
$aTemp = 0
WEnd
Else
; Create a transposed new array
Local $aTransposed = $oExcel.Application.WorksheetFunction.Transpose($vValue)
$oRange = $oWorksheet.Range($oWorksheet.Cells($vRangeOrRow, $iColumn), $oWorksheet.Cells($iLastRow, $iLastCol))
If $bValue = 1 Then
$oRange.Value = $aTransposed
Else
$oRange.Formula = $aTransposed
EndIf
If @error Then Return SetError(6, @error, 0)
EndIf
EndIf
Return 1
EndFunc ;==>_Excel_RangeWrite

Func Test_Main()
Local $rows = 65536
Local $cols = 30
Local $aData[$rows][$cols]
For $i = 0 To $rows - 1
For $j = 0 To $cols - 1
$aData[$i][$j] = "DATA_TEST_ROW_" & StringFormat("%05i", $i + 1)
Next
Next
Local $oExcel = _ExcelBookNew()
Local $Tim = TimerInit()
_Excel_RangeWrite($oExcel, Default, Default, $aData, 1)
MsgBox(0, "Speed", "Processing " & $rows & " rows and " & $cols & " columns took " & @CRLF & TimerDiff($Tim) & " milliseconds")

EndFunc ;==>Test_Main

[GMK]

I didn't see this until today, but I'll test it on Excel 2000 when I have opportunity.

[water]

I just posted it yesterday, so don't worry

[Spiff59]

Just nitpicking, and also personal opinion, but...

You have five function names beginning with "Excel_Range", and in 4 or 5 of them range is an optional parameter.

There are a ton of Autoit functions that have optional limiting parameters, yet those parameter names aren't morphed into the function name.

I think your function names would be better named simply Excel_Sort(), Excel_Read(), Excel_Find(), etc.

[water]

Spiff59,

the function names in the new Excel UDF follow the same scheme as those in the OutlookEX UDF. First the object they process and then the function.

It might be a big change to what you had before but I think the function names now better describe what the function does. On the other hand a user has to be a bit more familiar with the concepts of Excel (ranges etc.).

What we have now is a proposal from my side and a very early alpha. If the rewritten Word UDF makes it into the next AutoIt beta version I'm going to discus the rewrite of the Excel UDF with the powers that be.

So there might be a lot of changes in the future - or none

[Noviceatthis]

does this work on office 2013??

[water]

Don't know. I only have Office 2010 available, so I can't test.

Run the example scripts that come with the UDF and see for yourself.

I would be glad for any feedback (positive and negative).

[Noviceatthis]

@water

I have returned with answers

It seems that the following examples you provided worked on Excel 2013:

_Excel_BookNew

_Excel_BookOpen

_Excel_BookOpenText

_Excel_BookSaveAs

_Excel_Export

_Excel_Open

_Excel_RangeRead

_Excel_RangeReplace

_Excel_RangeWrite

Unfortunately, the others did not

Hope this helps

Oh and btw, thanks for this, gonna save me alot of time

Edited March 28, 2013 by Noviceatthis

[water]

What do you mean by "the others did not"?

Do they crash, do the example scripts return any error messages or do you simply get wrong results?

[Noviceatthis]

_Excel_BookAttach

- Line 289 (File "C:\Program Files (x86)\AutoIt3\Include\Excel Rewrite.au3"):

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

$oWorkbook = ^ERROR

- Error: Incorrect number of parameters in function call

_Excel_BookClose

- Line 24 ("*Script Directory*_Excel_BookClose.au3"):

- _Excel_CellWrite($oAppl, $oWorkbook, Default, "Test", 1, 1)

^ERROR

- Error: Unknown Function Name

_Excel_BookSave

- Line 24 ("*Script Directory*_Excel_BookSave.au3"):

- _Excel_CellWrite($oAppl, $oWorkbook, Default, "Test", 1, 1)

^ERROR

- Error: Unknown Function Name

_Excel_Close

- Gives no error message, but just doesn't work, as in doesn't close excel;

Only sends the 2 messageboxes stipulating the number of instances of

excel running

_Excel_RangeFind

- Line 635 (File "C:\Program Files (x86)\AutoIt3\Include\Excel Rewrite.au3"):

- $aResult[$iIndex][1] = $oMatch.Name.Name

$aResult[$iIndex][1] = $oMatch.Name^ERROR

- Error: The requested action with this object has failed

Edited March 29, 2013 by Noviceatthis

[water]

Noviceatthis,

thanks a lot for this feedback! Will have a look as soon as I can get my hand on an Office 2013 CD.

[water]

_Excel_BookAttach: You need one of the newest AutoIt beta versions 3.3.9.2 or later

_Excel_BookClose.au3: That's a bug in the example scripts. Replace _Excel_CellWrite with _Excel_RangeWrite

_Excel_BookSave.au3: That's a bug in the example scripts. Replace _Excel_CellWrite with _Excel_RangeWrite

_Excel_Close: If Excel was already running when this example is being run Excel will still be active because _Excel_Close is called without the $bForceClose = True

_Excel_RangeFind.au3: If run with AutoIT 3.3.9.2 or later no error will arise

[water]

Functions in the new Excel UDF ask for up to 3 parameters for the Application, WorkBook and WorkSheet.

I would like to reduce this to a single parameter. If it is a

• WorkSheet object: This WorkSheet will be processed
• WorkBook object: The active WorkSheet of the specified WorkBook will be processed
• Application object: The active WorkSheet of the active WorkBook will be processed

What do you think? Does this make coding easier?

Please post your opinion.

[GMK]

Is it possible to differentiate between the three objects in such a given parameter?

[water]

I have an idea but didn't have the time to test yet.

[water]

I just tested. You can differentiate beetween the 3 object types:

#include

Global $Object = _ExcelBookNew()
ConsoleWrite("Object.Name: " & $Object.Name & @LF)
ConsoleWrite("ObjName($Object, 1): " & ObjName($Object, 1) & @LF & @LF)

$Object = $Object.ActiveWorkBook
ConsoleWrite("Object.Name: " & $Object.Name & @LF)
ConsoleWrite("ObjName($Object, 1): " & ObjName($Object, 1) & @LF & @LF)

$Object = $Object.ActiveSheet
ConsoleWrite("Object.Name: " & $Object.Name & @LF)
ConsoleWrite("ObjName($Object, 1): " & ObjName($Object, 1) & @LF & @LF)

will give you (on a german system):

Object.Name: Microsoft Excel
ObjName($Object, 1): _Application

Object.Name: Mappe2
ObjName($Object, 1): _Workbook

Object.Name: Tabelle1
ObjName($Object, 1): _Worksheet

Tested with AutoIt 3.3.8.1 and 3.3.9.4

[JonMay]

This is amazing!

I'm having an intermittent problem with _Excel_BookOpen. Sometimes it returns an error=4, extended 0 when attempting:

;Open Spreadsheet
MsgBox(0, "Data Entry", "You will need to copy your data from whatever file you have formatted it on, into the format that the Automator can use." & @CRLF & "Once you're done, click save, quit and come back here for the next step.")
Global $oAppl = _Excel_Open()
If @error <> 0 Then Exit MsgBox(16, "Error creating Excel", "Error creating the Excel application object. Please email all errors to enactus@swansea-union.co.uk" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\TeamDataSheet.xls")
If @error <> 0 Then
MsgBox(16, "Error Opening Workbook", "Error opening workbook 'TeamDataSheet.xls'. Please email all errors to enactus@swansea-union.co.uk" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close($oAppl)
Exit
EndIf

Exit

Edited April 10, 2013 by JonMay

[water]

4 - Readwrite access could not be granted. Workbook might be open by another users/task.