VB6 - Excel display error: "#######"

Question

tgifgemini 12 Junior Poster

17 Years Ago

I am importing data from access file to a spreadsheet. One of the input field is a "comment" field with a full paragraph of comments. Some of the target cells in the spreadsheet displays "##########". But when I click on the cell, the Menu bar on top of the spreadsheet displays a large amount of data (text values) about a paragraph long.
I am assuming the column/cell is too small to hold that amount of data. I have tried to expand the column, but it's not working

I tried using a MergeCell function to merge some cells so as to have room to stretch out the data, but It's giving me weired message that it can only display the top most data or some stuff like that.
How can I resolve this, any ideas?

Thanks everybody for your input.
tgifgemini.

visual-basic

3 Contributors
12 Replies
99 Views
3 Days Discussion Span
Latest Post 17 Years Ago Latest Post by QVeen72

AV Manoharan 0 Junior Poster

17 Years Ago

I am importing data from access file to a spreadsheet. One of the input field is a "comment" field with a full paragraph of comments. Some of the target cells in the spreadsheet displays "##########". But when I click on the cell, the Menu bar on top of the spreadsheet displays a large amount of data (text values) about a paragraph long.
I am assuming the column/cell is too small to hold that amount of data. I have tried to expand the column, but it's not working
I tried using a MergeCell function to merge some cells so as to have room to stretch out the data, but It's giving me weired message that it can only display the top most data or some stuff like that.
How can I resolve this, any ideas?
Thanks everybody for your input.
tgifgemini.

I know what went wrong.

But that will be babbling...
Thanks

regards
AV Manoharan

QVeen72 104 Posting Shark

17 Years Ago

Hi tgif,

I think that Display is bcoz of the Null Values in Database..
can u check and tell me...?

Regards
Veena

QVeen72 104 Posting Shark

17 Years Ago

Hi,

ok try this code:

objectExcel.Rows("1:1").RowHeight = 30.75
    With objectExcel.Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

Regards
Veena

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

tgifgemini 12 Junior Poster · Answer 1 · 2007-08-06T20:30:19+00:00

AV Manoharan,
I don't know what your problem is, but I suggest "YOU TAKE A CHILL PILL, CRAWL UNDER A ROCK AND STAY THERE".
Few days ago you picked unnecessary fight with Veena, today you are being sarcastic with me. Dude this wonderful website does not revolve around you so shut the "f*#k up". I wish the moderator of this site will redress and reprimand your uncivilized attitude.
Get lost dude.
tgifgemini.

tgifgemini 12 Junior Poster · Answer 2 · 2007-08-06T21:05:37+00:00

Good morning Veena.
Not quite. That field in the table has multiple lines of text data. I think, maybe the column is too small to display all the incoming data.

I am trying to use codes - (See below) to see if I can fix it.

xlWksht.Columns(9).EntireColumn.AutoFit

or
Expand the row height

As I said in my previous post, even though the cell displays "#######", when I click on the cell, the menu bar on top displays numerous lines of data, exactly as it appears in the table(database).
Thanks.
tgifgemini.

tgifgemini 12 Junior Poster · Answer 3 · 2007-08-06T21:06:20+00:00

Good morning Veena.
Not quite. That field in the table has multiple lines of text data. I think, maybe the column is too small to display all the incoming data.

I am trying to use codes - (See below) to see if I can fix it.

xlWksht.Columns(9).EntireColumn.AutoFit

or
Expand the row height

As I said in my previous post, even though the cell displays "#######", when I click on the cell, the menu bar on top displays numerous lines of data, exactly as it appears in the table(database).
Thanks.
tgifgemini.

tgifgemini 12 Junior Poster · Answer 4 · 2007-08-06T21:36:11+00:00

Veena,
I ran my program again. when I clicked on the cell that has "#######", the menu on top displayed this data exactly the way it appears here:

"3/21/2005: Compare this new file with the file created in the last pay cycle of 2004.

3/31/2005: The file was created and reviewed. The information needs to be revised due to the timing of the first BL cycle run of 12/29/2004.

4/20/2005: PL changed from MV to JB (along with 17 hours charged).

5/19/2005: Signoff was received from the user on 4/12 - Closed. "

I tried to manually expand the column and row, but the "#######" still didn't go away.
This is a tough one.
tgifgemini.

tgifgemini 12 Junior Poster · Answer 5 · 2007-08-06T21:37:14+00:00

Veena,
I ran my program again. when I clicked on the cell that has "#######", the menu on top displayed this data exactly the way it appears here:

"3/21/2005: Compare this new file with the file created in the last pay cycle of 2004.

3/31/2005: The file was created and reviewed. The information needs to be revised due to the timing of the first BL cycle run of 12/29/2004.

4/20/2005: PL changed from MV to JB (along with 17 hours charged).

5/19/2005: Signoff was received from the user on 4/12 - Closed. "

I tried to manually expand the column and row, but the "#######" still didn't go away.
This is a tough one.
tgifgemini.

tgifgemini 12 Junior Poster · Answer 6 · 2007-08-06T22:01:14+00:00

Hi Veena,
I've tried using the recent codes, but the "#####" is still there. What ponders me is that I manually tried to expand the column/cell but that has no effect either.
tgif.

tgifgemini 12 Junior Poster · Answer 7 · 2007-08-07T00:25:15+00:00

Hi Veena,
I thought of a way to solve this problem and eliminate all the headache:
Looking at the code below, you'll notice that all the field headers are on the same row:Firstname Lastname Salary Comments

What if I split the record so that firstname, lastname Salary are on the same line(row) and the comments is written on the next line(row) by itself and then I can merge the comment row with other cells so there's enough room for all the data to display?

This is how I want the records to display:

Firstname Lastname Salary
Comments:
"This employee is over 50 years old and have put in 25 years in this organization"
etc.

Do you understand where I'm going with this?

What is the code I can use to accomplish the above format.
Below is the module that writes the records on the spreadsheet:

ii = 6

Do Until rsin.EOF = True
       ii = ii + 1
       xlWksht.Cells(ii, 1).Value = rsin![Req No]
       xlWksht.Cells(ii, 2).Value = rsin![Description]
       xlWksht.Cells(ii, 3).Value = rsin![P L] & Chr(10) & rsin![Pgmr2] & Chr(10) & rsin![Pgmr3]
       xlWksht.Cells(ii, 4).Value = rsin![ClientName] & Chr(10) & rsin![Status]
       xlWksht.Cells(ii, 5).Value = "-" & Chr(10) & rsin![Per Hrs]
       xlWksht.Cells(ii, 6).Value = rsin![Hours] & Chr(10) & rsin![Tot Hrs]
       xlWksht.Cells(ii, 7).Value = rsin![Start Date] & Chr(10) & rsin![Start Date]
       xlWksht.Cells(ii, 8).Value = rsin![End Date] & Chr(10) & rsin![End Date]
       xlWksht.Cells(ii, 9).Value = "'" & rsin![Comments]
       rsin.MoveNext

Thanks.
tgif.

tgifgemini 12 Junior Poster · Answer 8 · 2007-08-07T00:26:02+00:00

Hi Veena,
I thought of a way to solve this problem and eliminate all the headache:
Looking at the code below, you'll notice that all the field headers are on the same row:Firstname Lastname Salary Comments

What if I split the record so that firstname, lastname Salary are on the same line(row) and the comments is written on the next line(row) by itself and then I can merge the comment row with other cells so there's enough room for all the data to display?

This is how I want the records to display:

Firstname Lastname Salary
Comments:
"This employee is over 50 years old and have put in 25 years in this organization"
etc.

Do you understand where I'm going with this?

What is the code I can use to accomplish the above format.
Below is the module that writes the records on the spreadsheet:

ii = 6

Do Until rsin.EOF = True
       ii = ii + 1
       xlWksht.Cells(ii, 1).Value = rsin![Req No]
       xlWksht.Cells(ii, 2).Value = rsin![Description]
       xlWksht.Cells(ii, 3).Value = rsin![P L] & Chr(10) & rsin![Pgmr2] & Chr(10) & rsin![Pgmr3]
       xlWksht.Cells(ii, 4).Value = rsin![ClientName] & Chr(10) & rsin![Status]
       xlWksht.Cells(ii, 5).Value = "-" & Chr(10) & rsin![Per Hrs]
       xlWksht.Cells(ii, 6).Value = rsin![Hours] & Chr(10) & rsin![Tot Hrs]
       xlWksht.Cells(ii, 7).Value = rsin![Start Date] & Chr(10) & rsin![Start Date]
       xlWksht.Cells(ii, 8).Value = rsin![End Date] & Chr(10) & rsin![End Date]
       xlWksht.Cells(ii, 9).Value = "'" & rsin![Comments]
       rsin.MoveNext
Loop

Thanks.
tgif.

QVeen72 104 Posting Shark · Answer 9 · 2007-08-07T12:26:12+00:00

Hi,

ok, u can use "Split" function to split all the comments with a new line within it.

this code may be help ful to u:

Dim j as Integer
ii=6
Do Until rsin.EOF = True
     Dim MyArr
       ii = ii + 1
       xlWksht.Cells(ii, 1).Value = rsin![Req No]
       xlWksht.Cells(ii, 2).Value = rsin![Description]
       xlWksht.Cells(ii, 3).Value = rsin![P L] & Chr(10) & rsin![Pgmr2] & Chr(10) & rsin![Pgmr3]
       xlWksht.Cells(ii, 4).Value = rsin![ClientName] & Chr(10) & rsin![Status]
       xlWksht.Cells(ii, 5).Value = "-" & Chr(10) & rsin![Per Hrs]
       xlWksht.Cells(ii, 6).Value = rsin![Hours] & Chr(10) & rsin![Tot Hrs]
       xlWksht.Cells(ii, 7).Value = rsin![Start Date] & Chr(10) & rsin![Start Date]
       xlWksht.Cells(ii, 8).Value = rsin![End Date] & Chr(10) & rsin![End Date]
       MyArr =Split(rsin("Comments") & "",vbcrlf)
       For j = 0 To UBound(MyArr)
           xlWksht.Cells(ii, 9).Value = MyArr(j)
           ii=ii+1
       Next
       rsin.MoveNext
Loop

this is just a Crude Code, u can Refine it...

Regards
Veena