Update the Excel UDF that comes with AutoIt

[nitekram]

OK, I will just comment out that line for now, and wait until your BETA runs on AutoIt 3.3.8.1 - AGAIN, Thank You very much for this rewrite, is makes loading excel files into arrays so much faster.

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]

The next alpha version will have the following additional functions:

• _Excel_RangeInsert: Insert empty cells, rows, columns before a specified range
• _Excel_RangeCopy: Copy a range (cells, rows, columns) to a specified range
• _Excel_RangeDelete: Delete cells, rows, columns
• _Excel_RangeMove: Move a range (cells, rows, columns) to a specified range

[water]

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

@nitekram: Sorry, still doesn't support AutoIt 3.3.8.1

Edited May 30, 2013 by water

[water]

Any comments on one of the Alpha versions?

[SpineEyE]

I can't extract the .au3-Files of Alpha 5, neither with Winzip nor with 7-zip nor some online-extractors.

With Alpha 4 I get the following error:

Excel Rewrite.au3(174,56) : ERROR: ObjGet() [built-in] called with wrong number of args.
        $oWorkbook = ObjGet("", $sCLSID_Workbook, $iCount + 1)
        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

Am I missing something?

 

Edit: Running with beta has worked, however I still cannot unzip Version 5, whereas it works with Version 4.

Edited May 30, 2013 by SpineEyE

[water]

Yes. You need to run the UDF wiht version 3.3.9.x of AutoIt (beta version).

Never had a problem with unpacking the ZIP archive. It is packed with the builtin Windows 7 packer.

[water]

Edit: Running with beta has worked, however I still cannot unzip Version 5, whereas it works with Version 4.

Just tried to download and unpack the ZIP file. I get an error too: 0x80004005 Unknown error.

I will re-create the ZIP file and post again.

Thanks!

[water]

I re-packaged the ZIP file - now works without problems for me.

[water]

The Excel UDF that comes with Autoit supports both A1 ("C1:D5") and R1C1 (2,2 for row 2, column 2) notation.

Example:

If you want to format the range from column A, row 1 to column D, row 7 you can specify

_ExcelNumberFormat($oExcel, $sFormat, "A1:D7")

 or

_ExcelNumberFormat($oExcel, $sFormat, 1, 1, 7, 4)

The rewritten Excel UDF will only support the first format ("A1"). But there will be a function to convert R1C1 to a range object which can be passed like an A1 range to each function.

Is removing support for R1C1 notation a problem for you?

I'm grateful for any feedback you can give!

[GMK]

Removing R1C1 notation wouldn't be a problem for me.

On another note, I'm wondering if the default parameter for $bDisplayAlerts (in _Excel_Open) should be True. Otherwise, if a script does not handle saving or closing the workbook and one closes the workbook before saving, there would be no prompt to save.

[water]

GMK,

thanks for your reply! I was thinking about this too.

The goal of the UDF is to support automation of Excel. If possible there should be no user interaction until otherwise defined by the user.

Saving changes is the default for _Excel_Close and _Excel_BookClose. So no data should be lost if default settings are used.

But this is still Alpha stage so I'm all ears for suggestions.

[DW1]

[...] there will be a function to convert R1C1 to a range object which can be passed like an A1 range to each function.

 

Is removing support for R1C1 notation a problem for you?

As long as there is a function for R1C1 > Range, I wouldn't see a problem dropping direct support for R1C1.

Keep up the great work!

[water]

Thanks for your reply!

There will be functions available to translate in either direction.

[Andreu]

I'm obviously not one for the task of writing a UDF as extensive as this, so excuse my ignorance. I am curious though, as to the reason for dropping it.

While I haven't done a ton of autoit excel processing (though I'm working on something now)...I'm extremely well versed in using Excel itself, especially coupling it with VB. (How I normally program what I need done for my reports at work.) Now though, I'm converting to utilize the power of all 3 combined.

Say I'm looping throw rows... Wouldn't it be a lot more tedious to have to convert to Range for each call? (Performance I don't imagine would be noticeably affected.)

Like, in my current script I'm building (AutoIt UDF)... I must loop through the sheet doing something of the sort _ExcelReadCell($oExcel, $oStart, $dcol)... This way, I can do a natural $var+= For loop down through until meeting my logical criteria.

Seems like an unnecessary extra step, so I'm just curious.

[water]

Dropping R1C1 notation reduces the number of parameters you have to pass to a function from 2 or even 4 to just 1.

The overhead of converting R1C1 to A1 or providing two different code sections is now (old UDF) being done in the functions.

To process all rows of a sheet I recommend to read the data into an array and process the array - much faster.

[Andreu]

Dropping R1C1 notation reduces the number of parameters you have to pass to a function from 2 or even 4 to just 1.

The overhead of converting R1C1 to A1 or providing two different code sections is now (old UDF) being done in the functions.

To process all rows of a sheet I recommend to read the data into an array and process the array - much faster.

Makes sense. Though, I did try that method on my current project. I deal with some very massive excel docs in my work... Trying to load these to an array was taking an extremely long time.

I think I recall reading up somewhere in this thread that your rewrite improved the speed of writing the sheets to an array? If that's so... I may have to rewrite my script to test the difference.

I've run several tests while optimizing my code, but the biggest constraint is reading it line by line... I timed each test, and recorded the data. Tomorrow when I wake up, I'll rewrite and get back to you on the difference. 

(I actually have a thread up requesting assistance optimizing code for a AutoIt driven report in Excel in General... but the code I posted is very, very out of date now as I've already made quite a few significant changes.)

[water]

Reading/Writing a sheet is quite slow in the old UDF because _ExcelCellRead/_ExcelWriteCell is used under the covers for every cell. The new UDF uses a single statement (if possible) to transfer the range.

Hence the new functions are 20 to 50 times faster.

The new UDF isn't yet fully optimized (it is still an alpha). The RangeRead/Write functions still might crash if the range is too big because the Excel transpose method only supports 65536 rows.

[Andreu]

Well I'll definitely take the time to rewrite then. My program processes 7 reports, 2 sheets each (both being several, several thousands lines long)... And at the beginning... processed all 7 reports in 11.2 minutes. (And it's only that fast because I have 1 Parent running 7 Child programs, each handling a report under Parent's supervision.)

Rewrote a bunch, got it at 8.3 but nothing more can be done with Read/Write limitations and how big the reports are. 

Btw, Excel can only support 65,536 rows (and 256) per sheet... If a range is bigger than that idk how/why it's even being passed to the UDF lol. I've actually come close to hitting these walls before :S.

[water]

As far as I know the limit of 65,536 rows and 256 columns has been lifted with Excel 2007.

If a range exceeds the limits of the transpose method the function will manually transpose the array in the future.

[water]

BTW: >Here and >here are a few numbers on the speed difference.