Jump to content
Keel

Excel Range Write Charcaters Limit

Recommended Posts

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

Share this post


Link to post
Share on other sites
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).


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Keel
Posted (edited)

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:

#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 by Keel

Share this post


Link to post
Share on other sites
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)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Keel

Hi Water,

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

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

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Keel

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

Share this post


Link to post
Share on other sites
water
Posted (edited)

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

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

×