Excel Range Write Charcaters Limit

[Keel]

Hello,

I am trying to overcome characters limit set by Excel Range Write Function. I have found a thread related to this, but the person that were asking use Array, and I don't use any array in my script. I tried to set the $bForceFunc = True, but its does not solve it.

As u can see from my script below, essential 2 is the value that I copied from Doc 1 and many of it contains more than 250 characters. If the value has less than that, it copied perfectly. Otherwise, it will left blank. Need your help on this.

help.txt

[water]

Need to have a look at the code of the function. But I fear the transpose function is only used when an array is passed.
You could create an array with just one row/column and copy the variable into this array before writing to the target workbook (and set $bForceFunc to True).

[Keel]

Hi Water,

Thank you for your reply and suggestion. I tried passing the variable to an array, but it still does not work. I am not sure which part is making the error. Below is my script:

expandcollapse popup

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

Local $sSourcePath = "C:\Users\Desktop\Scripts"
    If FileExists($sSourcePath) = 0 Then DirCreate($sSourcePath)
Local $sTargetPath = "C:\Users\Desktop\Scripts\"
    If FileExists($sTargetPath) = 0 Then DirCreate($sTargetPath)

Local $sSourceBook = $sSourcePath & "\Doc 1.xlsx" 

Local $oExcel = _Excel_Open()
    If @error Then Exit MsgBox(4096, "Excel Error", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Local $oSourceBook = _Excel_BookOpen($oExcel, $sSourceBook)
    If @error Then
        MsgBox(4096, "Excel Error", "Error opening workbook '" & $sSourceBook & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

Local $oSourceRange, $oTargetBook, $sTargetBook, $x = 1

For $i = 4 To 53

    ;Copy & Paste essential 1
    $oSourceRange1 = _Excel_RangeRead($oSourceBook,"Doc1","E" & $i)
    $sTargetBook = $sTargetPath & "\" & $oSourceRange1 &".xlsx"
    $oTargetBook = _Excel_BookOpen($oExcel, $sTargetBook)
        If @error Then $oTargetBook = _Excel_BookNew($oExcel)
    _Excel_RangeWrite($oTargetBook, Default, $oSourceRange1, "C3")

    ;Copy & Paste essential 2
    $oSourceRange = _Excel_RangeRead($oSourceBook,"Doc1","D" & $i)
    Local $sArray1D [] = [$oSourceRange]
    $sTargetBook = $sTargetPath & "\" & $oSourceRange1 &".xlsx"
    $oTargetBook = _Excel_BookOpen($oExcel, $sTargetBook)
        If @error Then $oTargetBook = _Excel_BookNew($oExcel)
    _Excel_RangeWrite($oTargetBook, Default, $oSourceRange, "C4",True)


    ;Save and close test case book
    _Excel_BookSaveAs($oTargetBook, $sTargetBook, $xlWorkbookDefault)
    _Excel_BookClose($oTargetBook)

   $x += 1

Next

Edited May 23, 2018 by Keel

[water]

This reproducer script works fine for Excel 2016:

#include <Excel.au3>

; String 263 characters without spaces, 324 characters including spaces
$sString = "This string contains more than 255 characters. I do not know why this is necessary. Maybe it is just to test how Excel handles very long strings when writing to a cell by using function _Excel_RangeWrite. As the string is still too short I will add a few more words. Now the string is long enough so I will stop writing now."
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookNew($oExcel)
$iResult = _Excel_RangeWrite($oWorkbook, Default, $sString)
If @error Then MsgBox(0, "Range Write Error", "@error = " & @error & ", @extended = " & @extended)

 

[Keel]

Hi Water,

Thank you very much for your effort in composing all the words for the string .

However, here is the thing. The values that I failed to paste are actually a column of descriptions. It consists up to thousands of characters that resides in another workbook. So far my code only read the one that has less than 250 chars and paste them in new workbook, for each of the description.

I have tried passing them as a variable but it doesn't work. If i use array then it will take a lot of memory space and slow down the performance. Any other suggestions if  I may request?

Thank you

[water]

So the problem is not writing the cell but reading it?
Can you please set parameter $bForceFunc to True for function _Excel_RangeRead?

[Keel]

I did..for both read and write but still not working..

[water]

This works fine for me. Both reading and writing.

#include <Excel.au3>

; String 263 characters without spaces, 324 characters including spaces
$sString = "This string contains more than 255 characters. I do not know why this is necessary. Maybe it is just to test how Excel handles very long strings when writing to a cell by using function _Excel_RangeWrite. As the string is still too short I will add a few more words. Now the string is long enough so I will stop writing now."
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookNew($oExcel)
$iResult = _Excel_RangeWrite($oWorkbook, Default, $sString)
If @error Then MsgBox(0, "Range Write Error", "@error = " & @error & ", @extended = " & @extended)

$sString2 = _Excel_RangeRead($oWorkbook, Default, "A1", 1, True)
If @error Then MsgBox(0, "Range Write Error", "@error = " & @error & ", @extended = " & @extended)
ConsoleWrite(StringLen($sstring2) & @CRLF)

$iResult = _Excel_RangeWrite($oWorkbook, Default, $sString2, "A2")
If @error Then MsgBox(0, "Range Write Error", "@error = " & @error & ", @extended = " & @extended)

Which version of Excel do you run?

Edited May 24, 2018 by water