Keel Posted May 22, 2018 Share Posted May 22, 2018 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 Link to comment Share on other sites More sharing options...
water Posted May 22, 2018 Share Posted May 22, 2018 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 (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Keel Posted May 23, 2018 Author Share Posted May 23, 2018 (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: 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 Link to comment Share on other sites More sharing options...
water Posted May 23, 2018 Share Posted May 23, 2018 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 (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Keel Posted May 24, 2018 Author Share Posted May 24, 2018 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 Link to comment Share on other sites More sharing options...
water Posted May 24, 2018 Share Posted May 24, 2018 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 (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Keel Posted May 24, 2018 Author Share Posted May 24, 2018 I did..for both read and write but still not working.. Link to comment Share on other sites More sharing options...
water Posted May 24, 2018 Share Posted May 24, 2018 (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 May 24, 2018 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now