[RESOLVED] VB6 - Merge Cells in spreadsheet
Hello all,
I am populating a spreadsheet with data from access file. I am splitting each record into two lines so that it writes on two lines(some of the records writes on one row and the other half writes on the next row. I am doing this because some of the length of the records are pretty long to fit on one line. However, data in the second row bunches up in the first cell(column A) and doesn't spread out, making the height of that row to be extremely large, disfiguring the look of the spreadsheet.
What code I use to expand that second row so that the records spread out to other unused cells?
This is the code that generates the record I need to spread out to other cells.
Code:
xlWksht.Cells(ii + 1, 1).value = "'" & rsin![Comments]
Below is the code that populates the spreadsheet:
Code:
Do Until rsin.EOF = True
ii = ii + 2
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 + 1, 1).value = "'" & rsin![Comments]
rsin.MoveNext
Loop
GiftX
Re: VB6 - Merge Cells in spreadsheet
this is the third thread on the same problem, which i and others are trying to help you with, better if you keep all in one thread, so those who look can see all the information about the problem in one place
Re: VB6 - Merge Cells in spreadsheet
i have done some testing on a sheet, i used the code below to put a long string into a single cell, the entire string was visible across all the empty cells in the row. if the are any characters (even a space) in any cell in that row then the text will not show past that cell, so you need to check if any cells are not empty, even though you can see nothing, in any instance the #value did not show, just part of the text string, upto the first used cell
i thought there might be a limitation as to how many characters can go in a cell, but it would appear not or it is a very large number
code used Sheets("Sheet1").Cells(14, 1) = String(60000, "w")
at 70000 i get out of memory error
Quote:
Cell Error Values
You can insert a cell error value into a cell or test the value of a cell for an error value by using the CVErr function. The cell error values can be one of the following XlCVError constants.
Constant Error number Cell error value
xlErrDiv0 2007 #DIV/0!
xlErrNA 2042 #N/A
xlErrName 2029 #NAME?
xlErrNull 2000 #NULL!
xlErrNum 2036 #NUM!
xlErrRef 2023 #REF!
xlErrValue 2015 #VALUE!
Re: VB6 - Merge Cells in spreadsheet
Westconn1,
sorry for the multiple posting. As you can see, the error "#VALUE!" was resolved, followed by the display problem of "#####" which is a display problem. So, to get around this, I decided to split the record, writing data in the "comment" field in the next row. Invariably, I am writing one record in two rows. The problem now is the the comment field writes on the the second row as anticipated but because of the volume of the data in that cell, it expanded the height of the row. What I am trying to do is to stretch the comment so that it spreads accross other unused cells making it visible all accross. This is why I decided to start another thread so I don't mix one problem with the other.
in a nutshell, my problem now is to make sure the "comment" field lies parallel to othe half of the record.
This is how the spreadsheet is displaying:
Lastname Firstname Age Salary
--------- --------- ---- -----
Xtian Gift 30 500.00
Commet:
This is
where I
want the
comment to
be.
-------------------------------
This is how I want the spreadsheet to display:
Lastname Firstname Age Salary
--------- --------- ---- -----
Xtian Gift 30 500.00
Commet: This is where I want the comment to be.
As you can see, the comment field is stretched out in a single line, not bunched up as the previous one above.
Here is the code again.
Code:
ii = 5
Do Until rsin.EOF = True
ii = ii + 2
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 + 1, 1).Value = "Comments:" & Chr(10) & "'" & rsin![Comments]
rsin.MoveNext
Loop
I hope I'm making sense here.
Thanks.
GiftX
Re: VB6 - Merge Cells in spreadsheet
as i said in some post somewhere, post a sample spreadsheet with some comment lines in cells and someone will most likely look at it for you, but without an actual sample of what is put in the cell we are all just guessing
1 Attachment(s)
Re: VB6 - Merge Cells in spreadsheet
Good morning gentlemen,
Please see the spreadsheet attachment and see the horror in its display.
However, I've been able to accomplish my objective with this code:
Code:
xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).Merge
But now, the data in the cell is centered and the cell is astronomically large (occupies the entire screen)
Problem now is: (a) I want the data left-justified (b) and the cells minimized just enough so that the data fits.
Thanks all.
GiftX
1 Attachment(s)
Re: VB6 - Merge Cells in spreadsheet
PLEASE DISREGARD THE PREVIOUS POST. I UPLOADED THE WRONG SPREADSHEET.
Good morning gentlemen,
Please see the spreadsheet attachment and see the horror in its display.
However, I've been able to accomplish my objective with this code:
Code:
xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).Merge
But now, the data in the cell is centered and the cell is astronomically large (occupies the entire screen)
Problem now is: (a) I want the data left-justified (b) and the cells minimized just enough so that the data fits.
Thanks all.
GiftX
Re: VB6 - Merge Cells in spreadsheet
each comment cell contains 7 newline characters, so to make it fit on one line you need to remove those
vb Code:
xlWksht.Cells(ii + 1, 1).Value = Replace("Comments:" & Chr(10) & "'" & rsin![Comments], vbLF, vbtab) ' replace linefeed with tab
Re: VB6 - Merge Cells in spreadsheet
use this code to fix sample sheet
vb Code:
For i = 1 To 105
Cells(i, 1) = Replace(Cells(i, 1), vbLf, vbTab)
Next
Range("A1:A105").WrapText = False
Range("A1:A105").EntireColumn.AutoFit
Range("A1:A105").EntireRow.AutoFit
Re: VB6 - Merge Cells in spreadsheet
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:
Code:
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.:wave:
1 Attachment(s)
Re: VB6 - Merge Cells in spreadsheet
Hi Westconn1,
I tweaked my module using your codes. I got an output similar to what I'm looking for. However, I want the "comment" fields to wrap around (wrapText), left-justified and all the data to be seen both at display and when printed. Please take a close look of the tweaks I made.
Also, I see you hard-coded the number of rows in the "For" Loop
"For i = 1 to 105". The problem here is that the rows(number of records) is not fixed. It various depending on what the underlying query produces.
So can we make the number of records in the above "For" Loop dynamic (To depend on the number of rows with data)
See attachment of the current output.
Code:
ii = 5
Do Until rsin.EOF = True
ii = ii + 2
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 + 1, 1).Value = "Comments:" & Chr(10) & "'" & rsin![Comments]
xlWksht.Cells(ii + 1, 1).Value = Replace("Comments:" & Chr(10) & "'" & rsin![Comments], vbLf, vbTab) ' replace linefeed with tab
xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).Merge
xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).WrapText = True
xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).EntireRow.AutoFit
xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).HorizontalAlignment = xlLeft
rsin.MoveNext
Loop
xlApp.Visible = True
For i = 7 To 105
Cells(i, 1) = Replace(Cells(i, 1), vbLf, vbTab)
Next
Have a great weekend and a million thanks for all the help.
Same goes for you "Anhn".
GiftX
Re: VB6 - Merge Cells in spreadsheet
the hardcoded loop was just to fix the sample sheet and not needed when populating the sheet
the first post (1 line) should put all the data on one line
this code should get the display just as you want it
autofit did not work for the height of the row, so i had to calculate the height manually, this loop is to convert the sample sheet, but you should be able to incorporate it into your code to populate the sheet, rather than as a separate loop
vb Code:
Set s = Sheets("TblBiWeeklyPeriodExport")
For a = 8 To s.UsedRange.Rows.Count Step 2 ' first cell with comment to end
With s.Range(Cells(a, 1), Cells(a, 8))
If IsEmpty(s.Range("a" & a)) Then Exit For 'your used range was well below the data
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
' .AddIndent = False
.IndentLevel = 0
' .ShrinkToFit = False
.MergeCells = True
w = 0
For Each c In s.Range("A8:h8"): w = w + c.ColumnWidth: Next
.RowHeight = .RowHeight * Len(s.Range("A" & a).Text) / w
End With
Next
Re: VB6 - Merge Cells in spreadsheet
Hi Westconn1,
I've seen your recent code setup. It sure has the mark of a real pro. I couldn't have envisioned doing it that way.
However, I have a few questions:
I see you used variables: "a", "s", "w" and "c".
How did you Dim the above variable?
Code:
Dim s As object or sheet
Dim a As integer ?
Dim c As ??????
Dim w As integer?
Also, it's kinda confusing trying to web your code into mine. For simplicity, can I first populate the spreadsheet, then run your module separately?
Below is my current module setup:
Code:
ii = 5
Do Until rsin.EOF = True
ii = ii + 2
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 + 1, 1).Value = "Comments:" & Chr(10) & "'" & rsin![Comments]
xlWksht.Cells(ii + 1, 1).Value = Replace("Comments:" & Chr(10) & "'" & rsin![Comments], vbLf, vbTab) ' replace linefeed with tab
xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).Merge
xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).HorizontalAlignment = xlLeft
xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).WrapText = True
'xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).RowHeight = 70.25
xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).VerticalAlignment = xlTop
xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).IndentLevel = 0
xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).Orientation = 0
xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).ShrinkToFit = False
rsin.MoveNext
Loop
Thanks,
GiftX
Re: VB6 - Merge Cells in spreadsheet
Hi Westconn1,- PLEASE DISREGARD PREVIOUS POSTS: THIS POST IS CURRENTI've seen your recent code setup. It sure has the mark of a real pro. I couldn't have envisioned doing it that way.
However, I have a few questions:
I see you used variables: "a", "s", "w" and "c".
How did you Dim the above variable?
I didn't see where "c" was initially used or set?
Code:
Dim s As object or sheet
Dim a As integer ?
Dim c As ??????
Dim w As integer?
Also, it's kinda confusing trying to web your code into mine. For simplicity, can I first populate the spreadsheet, then run your module separately?
Below is my current module setup, please see if it will fly:
Code:
ii = 5
Do Until rsin.EOF = True
ii = ii + 2
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 + 1, 1).Value = "Comments:" & Chr(10) & "'" & rsin![Comments]
'xlWksht.Cells(ii + 1, 1).Value = Replace("Comments:" & Chr(10) & "'" & rsin![Comments], vbLf, vbTab) ' replace linefeed with tab
xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).Merge
xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).HorizontalAlignment = xlLeft
xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).WrapText = True
'xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).RowHeight = 70.25
xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).VerticalAlignment = xlTop
xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).Orientation = 0
rsin.MoveNext
Loop
xlWksht.Columns("B:B").ColumnWidth = 23
xlApp.Visible = True
Set s = Sheets("TblBiWeeklyPeriod.xls")
For a = 8 To s.UsedRange.Rows.Count Step 2 ' first cell with comment to end
With s.Range(Cells(a, 1), Cells(a, 8))
If IsEmpty(s.Range("a" & a)) Then
Exit For 'used range was well below the data
w = 0
For Each c In s.Range("A8:h8"): w = w + c.ColumnWidth: Next
.RowHeight = .RowHeight * Len(s.Range("A" & a).text) / w
End With
Next
Thanks,
GiftX
Re: VB6 - Merge Cells in spreadsheet
Dim s as Worksheet, a as long (or integer),c as range, w as long,
no need to set c when used as a for each, had to do that way as you can't get the width of the range of more than one cell
looks like i copied it wrong as the range is hard coded, but should be the same as the working range, but as all the cells are the same width it works anyway, so should be moved outside (before) the loop to improve speed
certainly you can populate the sheet first, then use this code to format it
as you already have a sheet set (xlWksht) you don't need to use or set s as it is only a duplicate
here is the code incorporated into your original code
note i havn't tested this as i can't, not have the database, but it looks right, not i moved the code to set the width of column b to the top as it would affect the width of the comment text if it is changed afterwards
vb Code:
ii = 5
xlWksht.Columns("B:B").ColumnWidth = 23
For Each c In xlWksht.Range("A8:h8"): w = w + c.ColumnWidth: Next
Do Until rsin.EOF = True
ii = ii + 2
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 + 1, 1).Value = "Comments:" & Chr(10) & "'" & rsin![Comments]
With xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8))
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
' .AddIndent = False
.IndentLevel = 0
' .ShrinkToFit = False
.MergeCells = True
.RowHeight = .RowHeight * Len(xlWksht.Range("A" & a).Text) / w
End With
'xlWksht.Cells(ii + 1, 1).Value = Replace("Comments:" & Chr(10) & "'" & rsin![Comments], vbLf, vbTab) ' replace linefeed with tab
''' xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).Merge
''' xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).HorizontalAlignment = xlLeft
''' xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).WrapText = True
''' 'xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).RowHeight = 70.25
''' xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).VerticalAlignment = xlTop
''' xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).Orientation = 0
rsin.MoveNext
Re: VB6 - Merge Cells in spreadsheet
Good morning Westconn1.
I modified my code with yours. During the execution, at the tail end I got a "Run-Time error: 1004 - Application-defined or object-defined error" on this line of code:
Code:
.RowHeight = .RowHeight * Len(xlWksht.Range("A" & a).text) / w
Do you think we should do the calculation separately before asigning it to .Rowheight?
Thanks.
GiftX
Re: VB6 - Merge Cells in spreadsheet
oops a is not used here just what i copied from my code before
change ("A" & a) to ("A" & ii+1)
i think that should fix it
or maybe ("A" & cstr(ii + 1))
1 Attachment(s)
Re: VB6 - Merge Cells in spreadsheet
Good morning Westconn1,
I modified the code accordingly and got another "Run-Time error 1004 - Unable to set the RowHeight property of the range class" on the code below.
Also, please look at the spreadsheet attachment. Is there any way I can get rid of the space(gap) that separates the line of text in the "Comment" row?
Code that caused Runtime error:
Code:
.RowHeight = .RowHeight * Len(xlWksht.Range("A" & CStr(ii + 1)).text) / w
And
.RowHeight = .RowHeight * Len(xlWksht.Range("A" & ii + 1).text) / w
1 Attachment(s)
Re: VB6 - Merge Cells in spreadsheet
Hi Westconn1,
Note: See current spreadsheet.
I have been able to accomplish one of my objective - getting rid of spaces(non printable characters)in the "Comments" field with this code:
Code:
xlWksht.Cells(ii + 1, 1).Value = "Comments:" & Chr(10) & "'" & xlApp.Clean(Trim(rsin![Comments]))
I also did some tweaking because of the "RunTime error 1004 unable to set the rowheight property of the range clas"
However, it ran okay, but the row height is still very high.
Is there any way to make the row height to fit the exact size of the data in the cell?
Below is what I did to get it working so far:
Code:
With xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8))
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.IndentLevel = 0
.MergeCells = True
MyRowHeight = .RowHeight * Len(xlWksht.Range("A" & CStr(ii + 1)).text) / w
If MyRowHeight > 409.5 Then
.RowHeight = 409.5
End If
End With
Thanks,
GiftX
Re: VB6 - Merge Cells in spreadsheet
Quote:
Also, please look at the spreadsheet attachment. Is there any way I can get rid of the space(gap) that separates the line of text in the "Comment" row?
when i replaced the linefeed in the comments i replaced it with a tab, hence the gap, you can change the tab to whatever is suitable, a space or a comma etc.
as far as the line height when i tested it it worked properly so i don't know what the problem is now
try it like this
vb Code:
ii = 5
xlWksht.Columns("B:B").ColumnWidth = 23
For Each c In xlWksht.Range("A8:h8"): w = w + c.ColumnWidth: Next
rht = xlWsht.Range("a6").RowHeight
Do Until rsin.EOF = True
ii = ii + 2
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 + 1, 1).Value = "Comments:" & Chr(10) & "'" & rsin![Comments]
With xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8))
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
' .AddIndent = False
.IndentLevel = 0
' .ShrinkToFit = False
.MergeCells = True
.RowHeight = rht * Len(xlWksht.Range("A" & a).Text) / w
End With
'xlWksht.Cells(ii + 1, 1).Value = Replace("Comments:" & Chr(10) & "'" & rsin![Comments], vbLf, vbTab) ' replace linefeed with tab
''' xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).Merge
''' xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).HorizontalAlignment = xlLeft
''' xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).WrapText = True
''' 'xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).RowHeight = 70.25
''' xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).VerticalAlignment = xlTop
''' xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).Orientation = 0
rsin.MoveNext
the problem is that the rowheight is already oversize, in the row that contains the comments so rht stores the base sixe for the row height an calculates from that
also i might have miss copied something when i posted so make sure there are no other changes apart from the ones for the rowheight
Re: VB6 - Merge Cells in spreadsheet
Good morning Westconn1,
Finally you nailed it.
I wish I can take you out for a drink. :D
I hope the moderators of this site knows that you are an asset.
Thanks again.
GiftX.
Re: [RESOLVED] VB6 - Merge Cells in spreadsheet
One important point to remember when you use mergecells is that the merge wont work if the contents of the grid are empty.If you dont want any contents to be displayed in the merged cells give a " " instead of "" (empty) content
Re: [RESOLVED] VB6 - Merge Cells in spreadsheet
Hi All,
i know this is very old post but i now not able to Merge a few cell in excel by use vb6 to create an excel.
i has upload my screenshot .
Regards
steve
