Entering multible lines into a single cell in Excel.

Business Central/NAV Developers Forum
1

Hi there,

I’m trying to export the content of Comment Line into a single cell in Excel using the Excel Buffer.

To achieve this i’ve done the following:

  • Added a boolean-field to the excel-buffer, that tells me that this cell is multiline. (i didn’t want to modify the Excel Buffer, to include a Line No. in the primary key, as i think i will f… other features up by that.)
  • Created a new table, with the same primary key as Excel Buffer, and also a Line No. in the key. (kind of like a header/line relationship)
  • Changed the code on function CreateSheet, in a way so that it loops through my new table, if the boolean-field i mentioned is set to TRUE.

As long as i only export a few lines, everything works fine.
However at some point, i seem to reach a limit in the amount of data i can put into a single cell. This limit is significantly less than i can into a cel, if i type it manually.

I hope this is because i use a wrong method, and that somehow i can get it to work, if only i knew wich method to use.
Right now i do it like this:
REPEAT
XlWrkSht.Range(xlColID + xlRowID).Value := FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value) +
ExcelBuffMultiLineRec.“Cell Value as Text” + FORMAT(CRLF);
UNTIL ExcelBuffMultiLineRec.NEXT = 0;
Anyone out there who can help me?
My guess is that there is an upper limit to the amount of data i can put into XlWrkSht.Range(x + y).Value.
Is there a XlWrkSht.Range(x + y).ValueAdd method?

2

What kind of error message did you get?

3

Hi,

I didn’t get any error-message.

I just didn’t get all the data i wanted into the cell.

I tried to export 25 lines, with 30 chars in each…
In that test, i changed property on my multi-line buffer table, to Temporary=False, just to check if all lines were created in that one, and they were.
I also tried to run the entire export with debugger - Breakpoint on triggers, toe check if the multiline-buffer were looped the correct amount of times, and it were.
But in the Excel-spread-sheet i only got 9 lines. (the last one however were actually 2½ line, that just didn’t get the CRLF, and therefore were truncated into one line.)

4

btw.

We’re using NAV5.0, with Excel 2007.

5

Well, you got about 8 or 9 lines with 30 chars each that is about 250 chars… - The field “Cell Value as Text” in the table 370 (Excel Buffer) occasionally has a length of 250 - may be there is a connection…

6

Well, the following code worked for me, showing a cell with 25 lines each of which containing 30 chars…

LF[1] := 10; // this is the NewLine in an excel cell

CREATE(xlapp,TRUE);
xlwrkbk := xlapp.Workbooks.Add;
xlwrksht := xlwrkbk.Worksheets.Add;

FOR i := 1 TO 25 DO BEGIN
IF i > 1 THEN
val := val + LF;
val := val + ‘ABCDEFGHIJABCDEFGHIJABCDEFGHIJ’;
END;

xlwrksht.Range(‘A1’).Value := val;

xlapp.Visible(TRUE);
xlapp.UserControl(TRUE);
CLEAR(xlapp);

// Variables: val=Text1000, LF=Text1, i=Integer, …, rest as usual

Indeed I got some trouble, too, if I tried to concatenate the string by using the Value property as you did above…
Perhaps the special character LF is not treated correctly when the data type is transformed from variant to text and vice versa…

7

Hi gerd,

First of all, thanks very much for taking the time to help me out here.

I was about to try something like what you do here, and now you have tried it for me, and determined that this way i can get more data put into the cell, than with my original approach.
And if this is the only way to do it, i surely will change my func. this way.
I don’t like it though…
As i mentioned, it is the comment line’s of a given service-order i need to put into the cell.
The solution you provide for me here, limits the number of chars i can transfer to Excel to 1000, wich still is a whole lot less than what i can put in there manually. Users can also put more than 1000 chars into the comment line-table.
But with this solution, i have to tell the user, that we have a limit of 1000 chars, that can be transfered to Excel. And that they have to put the info they need in Excel, in the first 1000 chars of the comment lines.
But if that’s the only way, then that’s how it’s gonna be…

8

May be it is possible to use the CONCATENATE function in Excel (the only function I have found to add two strings) …

…
xlwrksht.Range(‘A1’).Value := val + LF;
xlwrksht.Range(‘B1’).Value := val;
xlwrksht.Range(‘C1’).Formula := ‘=CONCATENATE(A1;B1)’;
…

This works in principle but unfortunately the CONCATENATE function seems to have a little bug (at least in Excel 2003), because all LFs are translated to some unreadable characters…

I think the problem mainly raises from the navision FORMAT function, which seems not to be able to work with more than 1000 chars…
Indeed try to run the following code with a Text1000 variable val:

FOR i := 1 TO 100 DO
val := val + ‘ABCDEFGHIJ’;

MESSAGE(val + val); // works properly
MESSAGE(FORMAT(val + val)); // gives a stack overflow !

9

Hi Gerd,

I’ve decided to go with the 1000 char-limit, and have had my client accept that limit.

The CONCATENATE-solution though could have made the soluiton better (if it could have read the LF-char correct though [:D])
I believe i can send upto 1000 chars into a single cell (based on your test earlier, thx), and the limit for the CONCATENATE-formula (as i see it from the excel-help) is 30 cells…
This would have given me the opportunity to have 30.000 chars transferred, insted of the 1000 i have to accept. [li]

thx again for your effort.