473,326 Members | 2,010 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How to write more than 256 characters to an Excel cell?

Hi guys,

I had a problem while writing into an Excel file.
What i want to do is:
i want to write the contents of 2 variables in one cell of the Excel field.

to write the contents of one variable, i use
$worksheet->write_string($row, $col, $value1, $format);
this works fine.

but to write 2 variables, if i use the following code then the 2nd variable is overwritten on the first one.
$worksheet->write_string($row, $col, $value1, $format);
$worksheet->write_string($row, $col, $value2, $format);


is there any way of appending a cell by another variable? So we can write the first variable, n then append the second variable.

It would be nice if some1 could help me out.

Any Help is appreciated.

Thank You
Jul 24 '07 #1
11 10870
numberwhun
3,509 Expert Mod 2GB
Just an idea, but what about creating a variable that contains the two, concatenated values? As such:

Expand|Select|Wrap|Line Numbers
  1.       my $value3 = $value1 . ", " . $value2;
  2.  
I don't have much experience with writing to excel spreadsheets, or any for that fact. If there is a way to tell if the cell already has a value, then maybe you can grab that value, use the above code to append your new data, then write to the cell with the new value. Like I said, just an idea.

Regards,

Jeff
Jul 24 '07 #2
Thanks for that Jeff

That idea did cross me, but the thing is a variable in perl can have max of 256 characters.
n in my application, the combined text in the variables $value1 and $value2 is more than 256.
so on concatenating, the remaining contents(above 256) will be lost.
my $value3 = $value1 . ", " . $value2;
$value3 will be able to hold only 256 char, rest will be lost.

so this option cant be considered.
We need to append $value2 to that particular cell only.

Thanks
Jul 24 '07 #3
miller
1,089 Expert 1GB
a variable in perl can have max of 256 characters.
For the third time, this is NOT true.

They excel interface that you are using may have such a limit, or the excel sheet itself may have such a limit, but perl has no limit on the size of variables. period.

- Miller
Jul 24 '07 #4
Oh ya.

sorry about that.
you are right, there is no size limit for a variable in Perl. Just checked it

I think i there is some issue with the Excel file.

Lemme find out.

Thanks very much for this
Jul 24 '07 #5
numberwhun
3,509 Expert Mod 2GB
For the third time, this is NOT true.

They excel interface that you are using may have such a limit, or the excel sheet itself may have such a limit, but perl has no limit on the size of variables. period.

- Miller
***holds back Miller***


I would run if I were you, I don't know how long I can hold him back.

(he he, sorry, I just had to)
Jul 24 '07 #6
miller
1,089 Expert 1GB
Very funny Jeff :)

Yes, I do get annoyed when people fail to listen. But I think I was more than nice enough all considering.

thanawala27,

Feel free to let us know what the real problem is when you discover it.

- Miller
Jul 24 '07 #7
well, i found out that variables take 256 characters. Fine with it.

but while putting a text in an Excel file, it does not take more than 256 characters(including spaces).

I tried to hardwired a text into a variable which is more than 256 characters.
while putting that into excel file, it took jus the first 256 characters. remaining were discarded.

Any idea about this.!!!

Something urgent folks. Plz try to help me out here

Thanks
Jul 24 '07 #8
numberwhun
3,509 Expert Mod 2GB
To me, that sounds like a limitation with Excel and (obviously) not Perl related. You may want to find a Microsoft related forum to see if that is something that can be changed in Excel, although I don't know if that's possible. (Not to slam them, but to Micro$oft, "issue" like these are features, not bugs).

Suggestions? Well, are you tied to Excel, or would you be able to try another spreadsheet? I don't know if it would have the same limitations, but try downloading OpenOffice and see if its spreadsheet program is as limited.

Regards,

Jeff
Jul 24 '07 #9
numberwhun
3,509 Expert Mod 2GB
Very funny Jeff :)

Yes, I do get annoyed when people fail to listen. But I think I was more than nice enough all considering.

thanawala27,

Feel free to let us know what the real problem is when you discover it.

- Miller
Thanks! I saw the opportunity for a little humor in this crazy day and dove head first at it. Nothing meant, all in good fun.

I agree though, that it is rather annoying when you give advice and/or direction, only to have it completely disregarded by the person you are/were advising. And yes, you were nice, but I could somehow feel and understand the frustration. I don't blame you at all. How many times I have we said "Please post your code"?

Regards,

Jeff
Jul 24 '07 #10
well, i found out that variables take 256 characters. Fine with it.

but while putting a text in an Excel file, it does not take more than 256 characters(including spaces).

I tried to hardwired a text into a variable which is more than 256 characters.
while putting that into excel file, it took jus the first 256 characters. remaining were discarded.

Any idea about this.!!!

Something urgent folks. Plz try to help me out here

Thanks
I do not think that there is such a limitation for Excel.

Please try this code. Look at the output of the command and check the character count in the resulting Excel sheet.

Expand|Select|Wrap|Line Numbers
  1. use Win32::OLE;
  2.  
  3. my $Excel = Win32::OLE->new('Excel.Application');
  4. $Excel->{SheetsInNewWorkbook} = 1;
  5. $Excel->{Visible} = 1;
  6.  
  7. my $Book = $Excel->Workbooks->Add;
  8. my $Sheet = $Book->Worksheets(1);
  9. $Sheet->{Name} = 'Sample';
  10.  
  11. my $text = "abcdefghijklmnopqrstuvwxyz" x 44;
  12.  
  13. print "Length is " . length($text) . "\n";
  14.  
  15. $Range = $Sheet->Range("A1:C1");
  16.  
  17. $Range->{1} = $text;
  18. $Range->{2} = $text;
  19. $Range->{3} = $text;
  20.  
-Dilip
Jul 25 '07 #11
Just format cell as general, not text
Oct 20 '10 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Paul | last post by:
I was wondering if anyone has had an issue where using vba code to read an excel file and import the data into an access table some records are not imported from the excel file. It seems looking at...
11
by: Mr. Smith | last post by:
Hello all, My code can successfully open, write to, format and save several worksheets in a workbook then save it by a given name, close and quit excel. My problem is that if I try and do it...
18
by: Frank M. Walter | last post by:
Hello, I have made an small AddIn with udf for excel 2003. I use vs2003. The point of view is the function __T() I call it in excel sheet writing =__T() I am not able to set a value to a...
0
by: Grip | last post by:
Hi, I have gone throught the group and Microsoft's online help and have seen many suggestions but I am still seeking clarity: 1. I have an excel spreadsheet. Column A contains text that may...
1
by: Mike P | last post by:
What is the maximum cell length in Excel 2000? I am writing a large text field to Excel and it seems to break it up into different cells after about 400 characters and then breaks everything...
0
by: acarrazco | last post by:
Hello, I am totaly new to VBA and I'm trying to modify a macro that was given to me but it doesn't seem to be working. I'm trying to extract data from three excel spreadsheets, put it into a combined...
1
by: thanawala27 | last post by:
Hi, I wanted to append a cell of an Excel file. Basically, the excel file is not taking more than 256 characters for a cell at a time. So i thought of breaking the varaible into 2. n copying it...
0
by: HughManity | last post by:
I was sent an Excel xls file where the Address column consists of up to 3 address lines stacked on top on each other - all in one cell. If there is more than one line in a cell, then all but the...
7
by: TG | last post by:
hi! I am trying to create a sql server table from an excel sheet. Here is the code I have: 'This procedure the xlsx file and dumps it to a table in SQL Server
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.