Too many "lines" for Excel? Transpose error on a spreadsheet

[TheDcoder]

Hello everyone ,

I have spotted a weird problem with the Excel COM API. It doesn't read a spreadsheet with a good amount of lines in a cell... Enough talk, let's get into code:

#include <Array.au3>
#include <Excel.au3>

Global $oExcel = _Excel_Open(False, False, False, False, True)
Global Const $sSpreadsheet = @ScriptDir & '\Test.xlsx'
Global $oSpreadsheet = _Excel_BookOpen($oExcel, $sSpreadsheet, True, False)
Global $aSpreadsheet = _Excel_RangeRead($oSpreadsheet)
If @error Then MsgBox(0, "Error", 'Error Code: ' & @error & @CRLF & 'Extended Code: ' & @extended)
_Excel_Close($oExcel, False)

_ArrayDisplay($aSpreadsheet)

(Download Test.xlsx)

I get this error when I run the script:

If you open the spreadsheet, you can see that I have dummy text in the B2 cell. If you replace the contents of the cell B2 with something like Test4, it will work perfectly fine:

 

Is this some kind of undocumented line limit for Excel?

Thanks in Advance! TD

[iamtheky]

this, from the rangeread helpfile?

  Quote

$bForceFunc

[optional] True forces to use the _ArrayTranspose function instead of the Excel transpose method (default = False).     See the Remarks section for details.

Expand  

 

[TheDcoder]

Wow, I didn't notice it at all! .

Thank you very much!

P.S I am still interested in why Excel wasn't able to transpose it by itself

[water]

Maybe you hit this limitation?

  Quote

The Excel transpose method doesn't support cells with more than 255 characters.

Expand  

 

[TheDcoder]

 Maybe I am blind... Thank you @water!

Edited June 30, 2017 by TheDcoder

[water]