Unexpected _Excel_RangeWrite Error 5

[Joboy2k]

I'm not sure whether i am doing something completely wrong but i keep getting Error 5 with extended error of -2147352567 while using _Excel_RangeWrite

I have tracked it down to being a character limit in a single cell. I read the help file where it mentions a limit of 255 characters unless i use the _arraytranspose method (which is what i have done and can't find any notes about limits when using that method)

 

I have created this script to replicate the issue, if a single variable is 8203 characters long it will work fine, but if it hits one that is 8204 characters long it then just abandons writing to the rest of the file even for variables with lower amounts.  I was hoping someone could  please guide me around this limit or maybe explain where the limit is coming from as i can manually open excel and write more than 8203 characters into a single cell.

 

Sorry if i am missing something very simple but i have been stuck on it for a while and cant seem to find an answer from the forum.  

 

Thank you in advance

#include <Excel.au3>

Global $Array[1][4]

$Array[0][0] = "1"

For $i = 1 to 8203
    $Array[0][1] &= "A"
Next

For $i = 1 to 8204
    $Array[0][2] &= "B"
Next

$Array[0][3] = 2

ConsoleWrite("String length of Array[0][1]: " & StringLen($Array[0][1]) & @CRLF)
ConsoleWrite("String length of Array[0][2]: " & StringLen($Array[0][2]) & @CRLF)

$Excel = _Excel_Open(False)
$ExcelBook = _Excel_BookNew($Excel)
_Excel_RangeWrite($ExcelBook, Default, $Array, "A1", True, True)
If @Error then
    MsgBox(0, "Error", "Error: " & @error & @CRLF & "Extended: " & @extended)
EndIf
_Excel_BookSaveAs($ExcelBook, @DesktopDir & "/Demo.xlsx")
If @Error then
    MsgBox(0, "Error", "Error saving excel file")
EndIf
_Excel_BookClose($ExcelBook)
_Excel_Close($Excel)
$Excel = ""
$ExcelBook = ""

 

 

Edited Thursday at 08:52 PM by Joboy2k

[water]

Your script runs without errors here.
I only had to set the overwrite parameter in _Excel_BooksSaveAs to True.

Which version of Excel do you run? The internal limits depend on the used version as described here.

Edited Friday at 08:51 AM by water

[Joboy2k]

Thats interesting. Thank you for taking the time to check. I am still running Excel 2007. according to that link i cant see the limit i am hitting. but the COM error i am getting of -2147352567 is the same COM error you get when opening a file with an incorrect password (according to that link) but that is obviously not what issue i am coming up against. the error is definitely from the _Excel_RangeWrite command. The limits on that link only mention limits of Rows and cell totals. The transpose limit of 255 characters shouldn't affect my script due to  $bForceFunc being set to true which then changes the limit to the limit of arrays.

Edited Friday at 11:40 AM by Joboy2k

[Nine]

FWIW, I tried with Excel 2003 and the limit is even lower (close to 1000 chars).  So I suppose it is an internal limit that cannot be exceeded when running automation script.

[water]

This site describes the problem you see and offers a working solution as well.
Seems you need to create a user function (like _Excel_RangeWriteSpecial), copy  _Excel_RangeWrite to your function and insert the lines described after "I was able to solve the error by adding .value to the copy."

1. First we need an AutoIt script to reproduce the problem with a string as described on the mentioned site
2. Next step is to translate the solution to AutoIt
3. Last step is to modify the solution for an array

I'm working on step 1 at the moment

 

Edited Friday at 02:36 PM by water

[water]

Step 1: Done
Step 2: Done

Could you please create 2 AutoIt scripts from the following code and test Error8204.au3 (and hopefully get an error msgbox as I did), then run Working8204.au3 and - keep your fingers crossed - get no error.
Please post your results  

expandcollapse popup

; Error8204.au3
#include <Excel.au3>
Local $sX, $oExcel, $oWorkBook, $oActiveWorksheet

For $i = 1 To 8204
    $sX &= "B"
Next
ConsoleWrite("String length of $sX: " & StringLen($sX) & @CRLF)

$oExcel = _Excel_Open()
If @error Then MsgBox(0, "Error", "_Excel_Open Error: " & @error & @CRLF & "Extended: " & @extended)
$oWorkBook = _Excel_BookNew($oExcel)
If @error Then MsgBox(0, "Error", "_Excel_BookNew Error: " & @error & @CRLF & "Extended: " & @extended)

; _Excel_RangeWrite($ExcelBook, Default, $Array, "A1") ; , True, True)
$oActiveWorksheet = $oWorkBook.ActiveSheet
If @error Then MsgBox(0, "Error", "Get active Sheet Error: " & @error & @CRLF & "Extended: " & @extended)
$oActiveWorksheet.Range("A1").Value = $sX
If @error Then MsgBox(0, "Error", "_Assign to Range A1 Error: " & @error & @CRLF & "Extended: " & @extended)
$oActiveWorksheet.Range("A2") = $oActiveWorksheet.Range("A1")
If @error Then MsgBox(0, "Error", "_Assign to Range A2 Error: " & @error & @CRLF & "Extended: " & @extended)


; Working8204.au3
#include <Excel.au3>
Local $sX, $oExcel, $oWorkBook, $oActiveWorksheet

For $i = 1 To 8204
    $sX &= "B"
Next
ConsoleWrite("String length of $sX: " & StringLen($sX) & @CRLF)

$oExcel = _Excel_Open()
If @error Then MsgBox(0, "Error", "_Excel_Open Error: " & @error & @CRLF & "Extended: " & @extended)
$oWorkBook = _Excel_BookNew($oExcel)
If @error Then MsgBox(0, "Error", "_Excel_BookNew Error: " & @error & @CRLF & "Extended: " & @extended)

$oActiveWorksheet = $oWorkBook.ActiveSheet
If @error Then MsgBox(0, "Error", "Get active Sheet Error: " & @error & @CRLF & "Extended: " & @extended)
$oActiveWorksheet.Range("A1").Value = $sX
If @error Then MsgBox(0, "Error", "_Assign to Range A1 Error: " & @error & @CRLF & "Extended: " & @extended)
$oActiveWorksheet.Range("A2") = $oActiveWorksheet.Range("A1").Value
If @error Then MsgBox(0, "Error", "_Assign to Range A2 Error: " & @error & @CRLF & "Extended: " & @extended)

 

[Joboy2k]

Brilliant Water. 

Error8204.au3 did get an error as you expected

_Assign to Range A2 Error: -2147352567, Extended 0

 

Working8204.au3 worked perfectly

I need to admit i don't understand why one is working and the other isn't. it will take me some time staring at your script to figure it out and hopefully use what you've done and create a new UDF of _Excel_RangeWrite that will work on excel 2007 for me.

Edited Friday at 05:13 PM by Joboy2k

[water]

Great!!

The difference between working and not working is the use of ".Value" in the assignment statement. Seems Excel is a bit overly precise here - but doesn't tell us  
Could you please run the following script? It's an extended version of your first script with added COM error handling. So we know if there are more problems we need to handle.
The error messages get written to the Console.

expandcollapse popup

#include <Excel.au3>

Global $aArray[1][4]
$aArray[0][0] = "1"
For $i = 1 to 8203
    $aArray[0][1] &= "A"
Next
For $i = 1 to 8204
    $aArray[0][2] &= "B"
Next
$aArray[0][3] = 2

ConsoleWrite("String length of Array[0][1]: " & StringLen($aArray[0][1]) & @CRLF)
ConsoleWrite("String length of Array[0][2]: " & StringLen($aArray[0][2]) & @CRLF)

Local $oError = ObjEvent("AutoIt.Error", "_ErrorHandler")

$Excel = _Excel_Open()
$ExcelBook = _Excel_BookNew($Excel)
_Excel_RangeWriteEX($ExcelBook, Default, $aArray, "A1", True, True)
If @Error then MsgBox(0, "Error", "Error _Excel_RangeWriteEX: " & @error & @CRLF & "Extended: " & @extended)

; #FUNCTION# ====================================================================================================================
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, PsaltyDS, Golfinhu, water
; ===============================================================================================================================
Func _Excel_RangeWriteEX($oWorkbook, $vWorksheet, $vValue, $vRange = Default, $bValue = Default, $bForceFunc = Default)
    ; Error handler, automatic cleanup at end of function
    ; Local $oError = ObjEvent("AutoIt.Error", "__Excel_COMErrFunc")
    #forceref $oError
    If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 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(2, @error, 0)
    ElseIf ObjName($vWorksheet, 1) <> "_Worksheet" Then
        Return SetError(2, @error, 0)
    EndIf
    If $vRange = Default Then $vRange = "A1"
    If $bValue = Default Then $bValue = True
    If $bForceFunc = Default Then $bForceFunc = False
    If Not IsObj($vRange) Then
        $vRange = $vWorksheet.Range($vRange)
        If @error Or Not IsObj($vRange) Then Return SetError(3, @error, 0)
    EndIf
    If Not IsArray($vValue) Then
        If $bValue Then
            $vRange.Value = $vValue
        Else
            $vRange.Formula = $vValue
        EndIf
        If @error Then Return SetError(4, @error, 0)
    Else
        If $vRange.Columns.Count = 1 And $vRange.Rows.Count = 1 Then
            If UBound($vValue, 0) = 1 Then
                $vRange = $vRange.Resize(UBound($vValue, 1), 1)
            Else
                $vRange = $vRange.Resize(UBound($vValue, 1), UBound($vValue, 2))
            EndIf
        EndIf
        If $bForceFunc Then
            _ArrayTranspose($vValue)
            If $bValue Then
                $vRange.Value = $vValue
            Else
                $vRange.Formula = $vValue
            EndIf
            If @error Then Return SetError(5, @error, 0)
        Else
            Local $oExcel = $oWorkbook.Parent
            If $bValue Then
                $vRange.Value = $oExcel.Transpose($vValue)
            Else
                $vRange.Formula = $oExcel.Transpose($vValue)
            EndIf
            If @error Then Return SetError(6, @error, 0)
        EndIf
    EndIf
    Return $vRange
EndFunc   ;==>_Excel_RangeWriteEX

; #INTERNAL_USE_ONLY# ===========================================================================================================
; Name ..........: _ErrorHandler
; Description ...: Called if an ObjEvent error occurs.
; Syntax.........: __Outlook_ErrorHandler()
; Parameters ....: None
; Return values .: @error is set to the COM error by AutoIt
; Author ........: water
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......:
; ===============================================================================================================================
Func _ErrorHandler()
    Local $sError = "COM Error Encountered in " & @ScriptName & @CRLF & _
            "@AutoItVersion = " & @AutoItVersion & @CRLF & _
            "@AutoItX64 = " & @AutoItX64 & @CRLF & _
            "@Compiled = " & @Compiled & @CRLF & _
            "@OSArch = " & @OSArch & @CRLF & _
            "@OSVersion = " & @OSVersion & @CRLF & _
            "Scriptline = " & $oError.Scriptline & @CRLF & _
            "NumberHex = 0x" & Hex($oError.Number, 8) & @CRLF & _
            "Number = " & $oError.Number & @CRLF & _
            "WinDescription = " & StringStripWS($oError.WinDescription, $STR_STRIPTRAILING) & @CRLF & _
            "Description = " & StringStripWS($oError.Description, $STR_STRIPTRAILING) & @CRLF & _
            "Source = " & $oError.Source & @CRLF & _
            "HelpFile = " & $oError.HelpFile & @CRLF & _
            "HelpContext = " & $oError.HelpContext & @CRLF & _
            "LastDllError = " & $oError.LastDllError
        ConsoleWrite($sError & @CRLF)
EndFunc   ;==>_ErrorHandler

 

[Joboy2k]

The Msgbox Error comes back with the standard

Error _Excel_rangewriteEx: 5, Extended: -2147352567

and this is the console output

String length of Array[0][1]: 8203
String length of Array[0][2]: 8204
COM Error Encountered in Excel Test.au3
@AutoItVersion = 3.3.18.0
@AutoItX64 = 1
@Compiled = 0
@OSArch = X64
@OSVersion = WIN_11
Scriptline = 67
NumberHex = 0x80020009
Number = -2147352567
WinDescription = Exception occurred.
Description = 
Source = 
HelpFile = 
HelpContext = 0
LastDllError = 0

 

[water]

Quick and Dirty: Your data gets written to a temporary Range and then copied to the range specified by you.
If this works thenthis code needs to be made more flexible and cleanup the temp. data etc.

expandcollapse popup

#include <Excel.au3>

Global $aArray[1][4]
$aArray[0][0] = "1"
For $i = 1 to 8203
    $aArray[0][1] &= "A"
Next
For $i = 1 to 8204
    $aArray[0][2] &= "B"
Next
$aArray[0][3] = 2

ConsoleWrite("String length of Array[0][1]: " & StringLen($aArray[0][1]) & @CRLF)
ConsoleWrite("String length of Array[0][2]: " & StringLen($aArray[0][2]) & @CRLF)

Local $oError = ObjEvent("AutoIt.Error", "_ErrorHandler")

$Excel = _Excel_Open()
$ExcelBook = _Excel_BookNew($Excel)
_Excel_RangeWriteEX($ExcelBook, Default, $aArray, "A1", True, True)
If @Error then MsgBox(0, "Error", "Error _Excel_RangeWriteEX: " & @error & @CRLF & "Extended: " & @extended)

; #FUNCTION# ====================================================================================================================
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, PsaltyDS, Golfinhu, water
; ===============================================================================================================================
Func _Excel_RangeWriteEX($oWorkbook, $vWorksheet, $vValue, $vRange = Default, $bValue = Default, $bForceFunc = Default)
    ; Error handler, automatic cleanup at end of function
    ; Local $oError = ObjEvent("AutoIt.Error", "__Excel_COMErrFunc")
    #forceref $oError
    If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 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(2, @error, 0)
    ElseIf ObjName($vWorksheet, 1) <> "_Worksheet" Then
        Return SetError(2, @error, 0)
    EndIf
    If $vRange = Default Then $vRange = "A1"
    If $bValue = Default Then $bValue = True
    If $bForceFunc = Default Then $bForceFunc = False
    If Not IsObj($vRange) Then
        $vRange = $vWorksheet.Range($vRange)
        If @error Or Not IsObj($vRange) Then Return SetError(3, @error, 0)
    EndIf
    If Not IsArray($vValue) Then
        If $bValue Then
            $vRange.Value = $vValue
        Else
            $vRange.Formula = $vValue
        EndIf
        If @error Then Return SetError(4, @error, 0)
    Else
        If $vRange.Columns.Count = 1 And $vRange.Rows.Count = 1 Then
            If UBound($vValue, 0) = 1 Then
                $vRange = $vRange.Resize(UBound($vValue, 1), 1)
            Else
                $vRange = $vRange.Resize(UBound($vValue, 1), UBound($vValue, 2))
            EndIf
        EndIf
        If $bForceFunc Then
            _ArrayTranspose($vValue)
            If $bValue Then
                Local $vRange2 = $vWorksheet.Range("A3:D3") ; Added
                $vRange2.Value = $vValue                    ; Added
                $vWorksheet.Range("A1:D1") = $vRange2.Value ; Added
;               $vRange.Value = $vValue                     ; Dropped
            Else
                $vRange.Formula = $vValue
            EndIf
            If @error Then Return SetError(5, @error, 0)
        Else
            Local $oExcel = $oWorkbook.Parent
            If $bValue Then
                $vRange.Value = $oExcel.Transpose($vValue)
            Else
                $vRange.Formula = $oExcel.Transpose($vValue)
            EndIf
            If @error Then Return SetError(6, @error, 0)
        EndIf
    EndIf
    Return $vRange
EndFunc   ;==>_Excel_RangeWriteEX

; #INTERNAL_USE_ONLY# ===========================================================================================================
; Name ..........: _ErrorHandler
; Description ...: Called if an ObjEvent error occurs.
; Syntax.........: __Outlook_ErrorHandler()
; Parameters ....: None
; Return values .: @error is set to the COM error by AutoIt
; Author ........: water
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......:
; ===============================================================================================================================
Func _ErrorHandler()
    Local $sError = "COM Error Encountered in " & @ScriptName & @CRLF & _
            "@AutoItVersion = " & @AutoItVersion & @CRLF & _
            "@AutoItX64 = " & @AutoItX64 & @CRLF & _
            "@Compiled = " & @Compiled & @CRLF & _
            "@OSArch = " & @OSArch & @CRLF & _
            "@OSVersion = " & @OSVersion & @CRLF & _
            "Scriptline = " & $oError.Scriptline & @CRLF & _
            "NumberHex = 0x" & Hex($oError.Number, 8) & @CRLF & _
            "Number = " & $oError.Number & @CRLF & _
            "WinDescription = " & StringStripWS($oError.WinDescription, $STR_STRIPTRAILING) & @CRLF & _
            "Description = " & StringStripWS($oError.Description, $STR_STRIPTRAILING) & @CRLF & _
            "Source = " & $oError.Source & @CRLF & _
            "HelpFile = " & $oError.HelpFile & @CRLF & _
            "HelpContext = " & $oError.HelpContext & @CRLF & _
            "LastDllError = " & $oError.LastDllError
        ConsoleWrite($sError & @CRLF)
EndFunc   ;==>_ErrorHandler

 

[Joboy2k]

Its only writing Array 0 and 1 (the string that is 8203 characters long) and fails at the 8204 long string. There is no Msgbox error which i was surprised at but this is the console output.

String length of Array[0][1]: 8203
String length of Array[0][2]: 8204
COM Error Encountered in Excel Test 2.au3
@AutoItVersion = 3.3.18.0
@AutoItX64 = 1
@Compiled = 0
@OSArch = X64
@OSVersion = WIN_11
Scriptline = 68
NumberHex = 0x80020009
Number = -2147352567
WinDescription = Exception occurred.
Description = 
Source = 
HelpFile = 
HelpContext = 0
LastDllError = 0

[water]

Strange 🤔
As we have a working solution for strings you could loop through the array and move cell by cell.
This will slow down your script, so it depends on the size of your array if this is a solution for you.

If our solution for strings does not work when passing an array cell, then please copy the array cell to variable and then call our solution.

Edited yesterday at 07:25 AM by water