[RESOLVED] VB6 - Merge Cells in spreadsheet

[anhn]

Giftx,

Someone already mentioned why you get #VALUE! error and gave you a solution by adding a ['] in front of "Comment", ie. x = "'" & Comment.
When adding a ['] in front of a value that forces Excel to treat that value as a text string instead of something else (a number or a date, etc).
That is fine, but you did more than that: after adding ['], you also add "Comment:" in front of it. If you intend to add the word "Comment:" then you don't need to add ['].

Now the next problem of "##############": the reason is like Access, a Text-formated cell in Excel only accepts maximum of 255 characters. If you look closely you will see that the string "##############" only appears when the Comment is longer than 255 characters. I beleive that the Comment field in Access is of data type Memo instead of Text.

To avoid this dump thing, you should do two things together: add a ['] in front of "Comment", then the cell that contains "Comment" must be formated as General instead of Text. I am sure you will never see "##############" again. However, another limitation of Excel you may see, if the "Comment" is very very long the display will be truncated, ie you only see the first n characters on the screen (n may be vary depend on version of Excel, I don't remember exactly but it is larger than 500).
There is an easy way for your table. Your code should be:

   Dim r As Long
   
   With xlWksht.Columns("I:I") '-- column I will be used for Comment
      .ColumnWidth = 60 '-- set width to larger if you want
      .NumberFormat = "General"
      .WrapText = True
   End With
   r = 5
   With rsin
      While Not .EOF
         r = r + 1
         xlWksht.Cells(r, 1).Value = ![Req No]
         xlWksht.Cells(r, 2).Value = ![Description]
         xlWksht.Cells(r, 3).Value = ![P L] & Chr(10) & ![Pgmr2] & Chr(10) & ![Pgmr3]
         xlWksht.Cells(r, 4).Value = ![ClientName] & Chr(10) & ![Status]
         xlWksht.Cells(r, 5).Value = "-" & Chr(10) & ![Per Hrs]
         xlWksht.Cells(r, 6).Value = ![Hours] & Chr(10) & ![Tot Hrs]
         xlWksht.Cells(r, 7).Value = ![Start Date] & Chr(10) & ![Start Date]
         xlWksht.Cells(r, 8).Value = ![End Date] & Chr(10) & ![End Date]
         xlWksht.Cells(r, 9).Value = "'" & rsin![Comments]
         .MoveNext
      Wend
   End With
   xlWksht.Rows("6:" & r).AutoFit

I am a new member, today is the first time I login. I hope the above can help you to solve your problem with a nice format of the worksheet as well.