Sign in to follow this  
Followers 0
KEHT

_Excel_RangeWrite won't write large strings

13 posts in this topic

Hello guys,
 
I've ran into a little problem that I don't know how to solve.  I am creating a 2-dimensional string array.  Some of the strings are somewhat large.  What I have noticed is that function _Excel_RangeWrite will not write an array where there's a string that exceeds 250+ characters.   I have ran into a similar problem when trying to do a replace on a Word document.  The workaround was to put a value on clipboard and then paste it.  I am fairly new to AutoIT and with a 2-d array I can't quite grasp how to proceed or whether it will even work.  Here is a code that I have, that won't put data in an Excel sheet.   If you shorten a first member of an $asActs array until the word "period", it will.  Any help and workaround is appreciated. Thanks!

#include <WindowsConstants.au3>
#include <StaticConstants.au3>
#include <ButtonConstants.au3>
#include <Excel.au3>
#include <array.au3>
#include <constants.au3>


Local $asActs[2][2] = [["PROVIDING FOR CONSIDERATION OF H. Res. 676, AUTHORIZATION TO INITIATE LITIGATION FOR ACTIONS BY THE PRESIDENT; PROVIDING FOR CONSIDERATION OF H.R. 935, REDUCING REGULATORY BURDENS ACT OF 2013; AND PROVIDING FOR PROCEEDINGS DURING THE PERIOD FROM AUGUST 1, 2014, THROUGH SEPTEMBER 5, 2014", "House Resolution 694"], _
["EXTENSION OF AFGHAN SPECIAL IMMIGRANT PROGRAM", "H.R. 5195"]]

Local $oExcel1 = _Excel_Open()
If @error Then Exit MsgBox($MB_ICONERROR, "Excel UDF: _Excel_Open General Leave", "Error creating a new Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookNew($oExcel1)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF:_Excel_BookNew General Leave", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel1)
    Exit
EndIf

$oExcel1.ActiveSheet.Columns("A:B").ColumnWidth = 40
If @error Then MsgBox(64, "Excel Bill Sheet", "Error " & @error & " returned by function '_ExcelRowHeightSet' on line " & @ScriptLineNumber)

_Excel_RangeWrite($oWorkbook, $oExcel1.ActiveSheet, $asActs, "A1")
If @error Then Exit MsgBox($MB_ICONERROR, "Excel UDF: _Excel_RangeWrite General Leave", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
$oExcel1.ActiveSheet.Columns("B:B").AutoFit

Share this post


Link to post
Share on other sites



Try using _Excel_RangeWrite with the $bForceFunc set to true, it works when using the _ArrayTranspose function but doesn't when using the $oExcel.Transpose method.

1 person likes this

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

Try using _Excel_RangeWrite with the $bForceFunc set to true, it works when using the _ArrayTranspose function but doesn't when using the $oExcel.Transpose method.

 

Great!!! It worked like a charm!!! Thanks a lot!!!   Didn't think it was going to be that easy. :)

Share this post


Link to post
Share on other sites

I'm guessing there's a limit on the amount of characters the .Transpose method can handle.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

had the same issue with range read


,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites

Correct. The Transpose method is limited in the number of cells it can handle and the length of any cell is limited to 255 characters.

But that is documented in the help file for _Excel_RangeRead and _ExcelRangeWrite :)


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

#7 ·  Posted (edited)

Would it be possible to get a specific error code when transpose fails?  Such that if I am doing this in a loop I could specify, "If this error then try again with the transpose flag set", or is there a better check?  As it stands now, anytime I am going to be reading an excel document, of which i do not know the contents, I have to run it with that flag set.  For me, that is like 95% of time.

Edited by boththose

,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites

_Excel_RangeRead already returns @error = 5 and _Excel_RangeWrite returns @error = 4 when Excel sets an error raised by the Transpose method.

2 people like this

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

_Excel_RangeRead already returns @error = 5 and _Excel_RangeWrite returns @error = 4 when Excel sets an error raised by the Transpose method.

So, @error = 4 is exclusively for Transpose method or it is rather generic for "other" writing errors?  Help files don't specify.  I guess I should've finished reading the function help, but it just didn't occur to me the whole Transpose business was relevant. :)

Share this post


Link to post
Share on other sites

It is a generic write error (at the moment).

Would it be sensible to have different error codes?

  • One for writing single cells (no transpose needed)
  • One for the Excel Transpose method.
  • Another for the internal _ArrayTranspose method

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.  I would totally appreciate that.


,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites

 

It is a generic write error (at the moment).

Would it be sensible to have different error codes?

  • One for writing single cells (no transpose needed)
  • One for the Excel Transpose method.
  • Another for the internal _ArrayTranspose method

 

 

I would have to agree that it would help immensely, especially for a relative noob to the language like myself.  I go by what debugger shows me and a meaning of errors due to lack of experience. The more specific the error message, the easier it is to identify a solution.  

Share this post


Link to post
Share on other sites

I will think about it :)


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

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