Update the Excel UDF that comes with AutoIt

[lolipop]

@@ DEBUG COM Error encountered in Test.au3 (1230) :
    Number          = 0x80020009 (-2147352567)
    WinDescription  = Exception occurred.
    Description     = Invalid number of arguments.
    Source          = Microsoft Excel
    HelpFile        = xlmain11.chm
    HelpContext     = 0
    LastDllError    = 0
    Retcode         = 0x800A03EC

[water]

Which version of Excel do you run?

Did you use Beta 4 or Beta 5 for the last test?

[lolipop]

i have tested on excel 2010 and 2013 with excel rewrite beta 4, beta 5 and even the latest autoit version which should already have the latest excel udf. All of them give me the same result.

Edited June 19, 2014 by lolipop

[water]

Very strange

Will test next week as soon as I return to my office.

[lolipop]

http://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/

The article above also mention that using range.text with multiple cell will return a null value. Seems to be the case now. Hope there is a solution to this.

[water]

I hate MS! That is information that should be found on MSDN in the Excel reference.

I will do some tests myself but I fear that your findings are correct and there is no way to get the text for more than a songle cell.

In this case the UDF docu needs to be enhanced.

[lolipop]

Thanks water. Await your further test and confirmation.

[water]

I just played with Excel 2010 and got the same result. You can access value and formula for a range > 1 cell. Text only allows one cell.

So I need to modify the function and enhance the documentation.

[lolipop]

Thanks for the confirmation. Sad to hear that. Do you know of any other ways to read multiple cell for displayed text? My whole excel data are mostly date and time. Else I would need to rewrite my whole excel date with text format.That's a pain in the ass.

[water]

I don't know of another way to read the text property of a larger range. You could do it in a loop and read cell by cell. But that would be much slower.

[illostos]

Hi,

i have a large Array with 12 columns and 23.000 rows.

When i try to Write the entire Array into an empty Excel Sheet nothing happens.

When i try my code on a smaller scale everything works fine.

#include <Array.au3>
#include <Excel.au3>
Local $Appl_Excel = _Excel_Open(1)                                                                                                                      ;1 = visible
Local $Var_Exceltabelle_Testspezifikation = (@ScriptDir & "\Template_Testspezifikation.xlsx")                               ;initialise
Local $Obj_Excel_Testspezifikation = _Excel_BookOpen($Appl_Excel, $Var_Exceltabelle_Testspezifikation, 0, 1)                        ;read+write+visible
Local $Array_Testspezifikation[2][12] = [["1","2","3"],["4","5"& @CRLF & "5.5","6"]]

_Excel_RangeWrite($Obj_Excel_Testspezifikation, Default , $Array_Testspezifikation)
_Excel_BookSaveAs ( $Obj_Excel_Testspezifikation, @ScriptDir & "\Testspezifikation.xlsx", $xlWorkbookDefault, True)

In this small scale everything works fine, but the exact same code with a 23.000 row array and nothing is written to the Excel Sheet.

When i try to look at my Array with _ArrayDisplay($Array_Testspezifikation) the Array looks just fine like i want it.

Can someone tell me why this is not working on a larger scale?

Edited July 2, 2014 by illostos

[water]

You need to add some error checking to your script.

What is the value of @error and @extended after you called _Excel_RangeWrite?

[illostos]

You need to add some error checking to your script.

What is the value of @error and @extended after you called _Excel_RangeWrite?

Hmm i have error checking

There are no errors. When i write the Array into the Excel Sheet "" is written.

I tried this with an Excel Sheet with data already in it. The Data is Overwritten with "".

Before _Excel_RangeWrite there is something in my Excel Sheet.

After _Excel_RangeWrite nothing is in my Excel Sheet.

But the Array i write into the Sheet is there, i see it with _ArrayDisplay. It can be displayed but it is not written into the Sheet.

[water]

Ok, but what is the value of @error and @extended after you called _Excel_RangeWrite?

[illostos]

Ok, but what is the value of @error and @extended after you called _Excel_RangeWrite?

 

@error = 0

@extended = 0

[water]

Which version of AutoIt do you run?

[illostos]

Which version of AutoIt do you run?

v3.3.12.0

PS: I tested something:

When i tried to Write my Array into a Textfile it works perfectly:

$sFilePath = @ScriptDir & "\Test.txt"
Local $Var_Test = FileOpen($sFilePath, 2)
_FileWriteFromArray($Var_Test, $Array_Testspezifikation)
FileClose($Var_Test)

But writing this to an Excel File doesn't work. Why?

Edit: OK i found the error. My Array was more than 255 characters which i didn't realised ... with the $bForceFunc=True it worked. Thanks for the fast support

Edited July 3, 2014 by illostos

[water]

I noticed that your data contains @CRLF.

I tested with the following script and it works just fine. Could you please test and post the result?

#include <Array.au3>
#include <Excel.au3>
Local $Appl_Excel = _Excel_Open(1)
Local $Var_Exceltabelle_Testspezifikation = (@ScriptDir & "\Template_Testspezifikation.xlsx")
Local $Obj_Excel_Testspezifikation = _Excel_BookNew($Appl_Excel)
Local $Array_Testspezifikation[26000][12] = [["1", "2", "3"],["4", "5" & @CRLF & "5.5", "6"]]
For $i = 0 To UBound($Array_Testspezifikation, 1) - 1
    $Array_Testspezifikation[$i][0] = $i + 1
    $Array_Testspezifikation[$i][1] = $i + 2 & @CRLF & $i + 2.5
    $Array_Testspezifikation[$i][2] = $i + 3
    $Array_Testspezifikation[$i][3] = $i + 4
    $Array_Testspezifikation[$i][4] = $i + 5
Next
_Excel_RangeWrite($Obj_Excel_Testspezifikation, Default, $Array_Testspezifikation)

[DerPensionist]

Missing very usefull/required  argument "TextToDisplay" of UDF "_Excel_RangeLinkAddRemove"

Is there any reason not implement this argument?

To add an significant short meaningfull content to the cell instead of very long URL.

Solution simple, only addconditional Argument.

[water]

Seems I missed that. Will be added to the next Beta version of AutoIt.