ExcelRewrite.au3 generates an error when the Cell having more than 255 characters

[Curious52]

Hi,

When I try to read Excel file cells with ExcelRewrite.au3, whenever a cell has more than 255 characters I have this error:

D:Program FilesAutoIt3IncludeExcel Rewrite.au3 (967) : ==> The requested action with this object has failed.:
$vResult = $oExcel.Transpose($vRange.Value)
$vResult = $oExcel.Transpose($vRange.Value)^ ERROR

What can I do to over come this issue?

Thanks for any help

 

[water]

Set parameter $bForceFunc of function _Excel_RangeRead to True. The internally used transpose method isn't able to handle more than 255 characters.

[Curious52]

Hi MVP,

I couldn't find where is the $bForceFunc in the _Excel_RangeRead, can you clarify it again? I attach its function here:

Func _Excel_RangeRead($oExcel, $oWorkbook, $vWorksheet = Default, $vRange = Default, $iReturn = Default)

 If Not IsObj($oExcel) Or ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, 0, 0)
 If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(2, 0, 0)
 If Not IsObj($vWorksheet) Then
  If $vWorksheet = Default Then
   $vWorksheet = $oWorkbook.ActiveSheet
  Else
   $vWorksheet = $oWorkbook.WorkSheets.Item($vWorksheet)
  EndIf
  If @error Or Not IsObj($vWorksheet) Then Return SetError(3, @error, 0)
 ElseIf ObjName($vWorksheet, 1) <> "_Worksheet" Then
  Return SetError(3, @error, 0)
 EndIf
 If $vRange = Default Then
  $vRange = $vWorksheet.Usedrange
 ElseIf Not IsObj($vRange) Then
  $vRange = $vWorksheet.Range($vRange)
  If @error Or Not IsObj($vRange) Then Return SetError(4, @error, 0)
 EndIf
 If $iReturn = Default Then
  $iReturn = 1
 ElseIf $iReturn < 1 Or $iReturn > 3 Then
  Return SetError(5, 0, 0)
 EndIf
 
 Local $vResult, $bManualTranspose = False
 
 ; The max number of elements in an AutoIt array is limited to 2^24 = 16,777,216
 If $vRange.Columns.Count * $vRange.Rows.Count > 16777216 Then Return SetError(7, 0, 0)
 ; Transpose has an undocument limit on the number of cells or rows it can transpose. This limit increases with the Excel version
 ; Limits:
 ;   Excel 97   - 5461 cells
 ;   Excel 2000 - 5461 cells
 ;   Excel 2003 - ?
 ;   Excel 2007 - 65536 rows ?
 ;   Excel 2010 - ?
 ; Example: If $oExcel.Version = 14 And $vRange.Columns.Count * $vRange.Rows.Count > 1000000 Then $bManualTranspose = True
 If $bManualTranspose Then
  ; The max number of elements in an AutoIt array is limited to 2^24 = 16,777,216
  ; _ArrayTranspose uses an n * n array where n is Max(rows, columns)
  Local $iMax = $vRange.Rows.Count
   If $vRange.Columns.Count > $vRange.Rows.Count Then $iMax = $vRange.Columns.Count
  If $iMax * $iMax > 16777216 Then Return SetError(7, $iMax * $iMax, 0)
  If $iReturn = 1 Then
   $vResult = $vRange.Value
  ElseIf $iReturn = 2 Then
   $vResult = $vRange.Formula
  Else
   $vResult = $vRange.Text
  EndIf
  If @error Then Return SetError(6, @error, 0)
  _ArrayTranspose($vResult)
 Else
  If $iReturn = 1 Then
   $vResult = $oExcel.Transpose($vRange.Value)
  ElseIf $iReturn = 2 Then
   $vResult = $oExcel.Transpose($vRange.Formula)
  Else
   $vResult = $oExcel.Transpose($vRange.Text)
     EndIf

  If @error Or Not IsArray($vResult) Then Return SetError(6, @error, 0)
 EndIf
 Return $vResult

EndFunc   ;==>_Excel_RangeRead

Thanks for help.

[water]

Looks like you use an older version < Beta 1 of the UDF.

Can you please download the latest version and try again?

[Curious52]

Yes, it workks fine now!

Thanks for helping, I really appreciate it