Sign in to follow this  
Followers 0
Curious52

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

5 posts in this topic

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

 

Share this post


Link to post
Share on other sites



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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Can you please download the latest version and try again?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Yes, it workks fine now!

Thanks for helping, I really appreciate it :thumbsup:

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0