_Excel_RangeWrite won't write large strings

[KEHT]

Hello guys,
 
I've ran into a little problem that I don't know how to solve.  I am creating a 2-dimensional string array.  Some of the strings are somewhat large.  What I have noticed is that function _Excel_RangeWrite will not write an array where there's a string that exceeds 250+ characters.   I have ran into a similar problem when trying to do a replace on a Word document.  The workaround was to put a value on clipboard and then paste it.  I am fairly new to AutoIT and with a 2-d array I can't quite grasp how to proceed or whether it will even work.  Here is a code that I have, that won't put data in an Excel sheet.   If you shorten a first member of an $asActs array until the word "period", it will.  Any help and workaround is appreciated. Thanks!

#include <WindowsConstants.au3>
#include <StaticConstants.au3>
#include <ButtonConstants.au3>
#include <Excel.au3>
#include <array.au3>
#include <constants.au3>


Local $asActs[2][2] = [["PROVIDING FOR CONSIDERATION OF H. Res. 676, AUTHORIZATION TO INITIATE LITIGATION FOR ACTIONS BY THE PRESIDENT; PROVIDING FOR CONSIDERATION OF H.R. 935, REDUCING REGULATORY BURDENS ACT OF 2013; AND PROVIDING FOR PROCEEDINGS DURING THE PERIOD FROM AUGUST 1, 2014, THROUGH SEPTEMBER 5, 2014", "House Resolution 694"], _
["EXTENSION OF AFGHAN SPECIAL IMMIGRANT PROGRAM", "H.R. 5195"]]

Local $oExcel1 = _Excel_Open()
If @error Then Exit MsgBox($MB_ICONERROR, "Excel UDF: _Excel_Open General Leave", "Error creating a new Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookNew($oExcel1)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF:_Excel_BookNew General Leave", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel1)
    Exit
EndIf

$oExcel1.ActiveSheet.Columns("A:B").ColumnWidth = 40
If @error Then MsgBox(64, "Excel Bill Sheet", "Error " & @error & " returned by function '_ExcelRowHeightSet' on line " & @ScriptLineNumber)

_Excel_RangeWrite($oWorkbook, $oExcel1.ActiveSheet, $asActs, "A1")
If @error Then Exit MsgBox($MB_ICONERROR, "Excel UDF: _Excel_RangeWrite General Leave", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
$oExcel1.ActiveSheet.Columns("B:B").AutoFit

[BrewManNH]

Try using _Excel_RangeWrite with the $bForceFunc set to true, it works when using the _ArrayTranspose function but doesn't when using the $oExcel.Transpose method.

[KEHT]

Try using _Excel_RangeWrite with the $bForceFunc set to true, it works when using the _ArrayTranspose function but doesn't when using the $oExcel.Transpose method.

 

Great!!! It worked like a charm!!! Thanks a lot!!!   Didn't think it was going to be that easy.

[BrewManNH]

I'm guessing there's a limit on the amount of characters the .Transpose method can handle.

[iamtheky]

had the same issue with range read

[water]

Correct. The Transpose method is limited in the number of cells it can handle and the length of any cell is limited to 255 characters.

But that is documented in the help file for _Excel_RangeRead and _ExcelRangeWrite

[iamtheky]

Would it be possible to get a specific error code when transpose fails?  Such that if I am doing this in a loop I could specify, "If this error then try again with the transpose flag set", or is there a better check?  As it stands now, anytime I am going to be reading an excel document, of which i do not know the contents, I have to run it with that flag set.  For me, that is like 95% of time.

Edited September 17, 2014 by boththose

[water]

_Excel_RangeRead already returns @error = 5 and _Excel_RangeWrite returns @error = 4 when Excel sets an error raised by the Transpose method.

[KEHT]

_Excel_RangeRead already returns @error = 5 and _Excel_RangeWrite returns @error = 4 when Excel sets an error raised by the Transpose method.

So, @error = 4 is exclusively for Transpose method or it is rather generic for "other" writing errors?  Help files don't specify.  I guess I should've finished reading the function help, but it just didn't occur to me the whole Transpose business was relevant.

[water]

It is a generic write error (at the moment).

Would it be sensible to have different error codes?

• One for writing single cells (no transpose needed)
• One for the Excel Transpose method.
• Another for the internal _ArrayTranspose method

[iamtheky]

Yes.  I would totally appreciate that.

[KEHT]

 

It is a generic write error (at the moment).

Would it be sensible to have different error codes?

• One for writing single cells (no transpose needed)
• One for the Excel Transpose method.
• Another for the internal _ArrayTranspose method

 

 

I would have to agree that it would help immensely, especially for a relative noob to the language like myself.  I go by what debugger shows me and a meaning of errors due to lack of experience. The more specific the error message, the easier it is to identify a solution.  

[water]

I will think about it