Results 1 to 23 of 23

Thread: [RESOLVED] VB6 - Merge Cells in spreadsheet

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    Resolved [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

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

    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!
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    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

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    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
    Attached Files Attached Files

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    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
    Attached Files Attached Files

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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:
    1. xlWksht.Cells(ii + 1, 1).Value = Replace("Comments:" & Chr(10) & "'" & rsin![Comments], vbLF, vbtab) ' replace linefeed with tab
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: VB6 - Merge Cells in spreadsheet

    use this code to fix sample sheet
    vb Code:
    1. For i = 1 To 105
    2. Cells(i, 1) = Replace(Cells(i, 1), vbLf, vbTab)
    3. Next
    4. Range("A1:A105").WrapText = False
    5. Range("A1:A105").EntireColumn.AutoFit
    6. Range("A1:A105").EntireRow.AutoFit
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Smile 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.
    Last edited by anhn; Aug 22nd, 2007 at 10:25 PM.

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    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
    Attached Files Attached Files

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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:
    1. Set s = Sheets("TblBiWeeklyPeriodExport")
    2. For a = 8 To s.UsedRange.Rows.Count Step 2 ' first cell with comment to end
    3. With s.Range(Cells(a, 1), Cells(a, 8))
    4. If IsEmpty(s.Range("a" & a)) Then Exit For  'your used range was well below the data
    5.         .HorizontalAlignment = xlLeft
    6.         .VerticalAlignment = xlTop
    7.         .WrapText = True
    8.         .Orientation = 0
    9. '        .AddIndent = False
    10.         .IndentLevel = 0
    11. '        .ShrinkToFit = False
    12.         .MergeCells = True
    13.         w = 0
    14.         For Each c In s.Range("A8:h8"): w = w + c.ColumnWidth: Next
    15.         .RowHeight = .RowHeight * Len(s.Range("A" & a).Text) / w
    16.  
    17. End With
    18. Next
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    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

  14. #14

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    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

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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:
    1. ii = 5
    2.     xlWksht.Columns("B:B").ColumnWidth = 23
    3.     For Each c In xlWksht.Range("A8:h8"): w = w + c.ColumnWidth: Next
    4.     Do Until rsin.EOF = True
    5.        ii = ii + 2
    6.        xlWksht.Cells(ii, 1).Value = rsin![Req No]
    7.        xlWksht.Cells(ii, 2).Value = rsin![Description]
    8.        xlWksht.Cells(ii, 3).Value = rsin![P L] & Chr(10) & rsin![Pgmr2] & Chr(10) & rsin![Pgmr3]
    9.        xlWksht.Cells(ii, 4).Value = rsin![ClientName] & Chr(10) & rsin![Status]
    10.        xlWksht.Cells(ii, 5).Value = "-" & Chr(10) & rsin![Per Hrs]
    11.        xlWksht.Cells(ii, 6).Value = rsin![Hours] & Chr(10) & rsin![Tot Hrs]
    12.        xlWksht.Cells(ii, 7).Value = rsin![Start Date] & Chr(10) & rsin![Start Date]
    13.        xlWksht.Cells(ii, 8).Value = rsin![End Date] & Chr(10) & rsin![End Date]
    14.        xlWksht.Cells(ii + 1, 1).Value = "Comments:" & Chr(10) & "'" & rsin![Comments]
    15.        With xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8))
    16.               .HorizontalAlignment = xlLeft
    17.               .VerticalAlignment = xlTop
    18.               .WrapText = True
    19.               .Orientation = 0
    20.       '        .AddIndent = False
    21.               .IndentLevel = 0
    22.       '        .ShrinkToFit = False
    23.               .MergeCells = True
    24.               .RowHeight = .RowHeight * Len(xlWksht.Range("A" & a).Text) / w
    25.       End With
    26.  
    27.        'xlWksht.Cells(ii + 1, 1).Value = Replace("Comments:" & Chr(10) & "'" & rsin![Comments], vbLf, vbTab) ' replace linefeed with tab
    28. '''       xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).Merge
    29. '''       xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).HorizontalAlignment = xlLeft
    30. '''       xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).WrapText = True
    31. '''       'xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).RowHeight = 70.25
    32. '''       xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).VerticalAlignment = xlTop
    33. '''       xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).Orientation = 0
    34.        rsin.MoveNext
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  16. #16

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    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

  17. #17
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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))
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  18. #18

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    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
    Attached Files Attached Files

  19. #19

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    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
    Attached Files Attached Files

  20. #20
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: VB6 - Merge Cells in spreadsheet

    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:
    1. ii = 5
    2.           xlWksht.Columns("B:B").ColumnWidth = 23
    3.           For Each c In xlWksht.Range("A8:h8"): w = w + c.ColumnWidth: Next
    4.          rht = xlWsht.Range("a6").RowHeight
    5.  
    6.           Do Until rsin.EOF = True
    7.              ii = ii + 2
    8.              xlWksht.Cells(ii, 1).Value = rsin![Req No]
    9.              xlWksht.Cells(ii, 2).Value = rsin![Description]
    10.              xlWksht.Cells(ii, 3).Value = rsin![P L] & Chr(10) & rsin![Pgmr2] & Chr(10) & rsin![Pgmr3]
    11.  
    12.              xlWksht.Cells(ii, 4).Value = rsin![ClientName] & Chr(10) & rsin![Status]
    13.              xlWksht.Cells(ii, 5).Value = "-" & Chr(10) & rsin![Per Hrs]
    14.              xlWksht.Cells(ii, 6).Value = rsin![Hours] & Chr(10) & rsin![Tot Hrs]
    15.              xlWksht.Cells(ii, 7).Value = rsin![Start Date] & Chr(10) & rsin![Start Date]
    16.              xlWksht.Cells(ii, 8).Value = rsin![End Date] & Chr(10) & rsin![End Date]
    17.              xlWksht.Cells(ii + 1, 1).Value = "Comments:" & Chr(10) & "'" & rsin![Comments]
    18.              With xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8))
    19.                     .HorizontalAlignment = xlLeft
    20.                     .VerticalAlignment = xlTop
    21.                  .WrapText = True
    22.                     .Orientation = 0
    23.             '        .AddIndent = False
    24.                     .IndentLevel = 0
    25.             '        .ShrinkToFit = False
    26.                     .MergeCells = True
    27.                     .RowHeight = rht * Len(xlWksht.Range("A" & a).Text) / w
    28.             End With
    29.        
    30.              'xlWksht.Cells(ii + 1, 1).Value = Replace("Comments:" & Chr(10) & "'" & rsin![Comments], vbLf, vbTab) ' replace linefeed with tab
    31.       '''       xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).Merge
    32.       '''       xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).HorizontalAlignment = xlLeft
    33.       '''       xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).WrapText = True
    34.       '''       'xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).RowHeight = 70.25
    35.       '''       xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).VerticalAlignment = xlTop
    36.       '''       xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).Orientation = 0
    37.              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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  21. #21

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    Re: VB6 - Merge Cells in spreadsheet

    Good morning Westconn1,
    Finally you nailed it.
    I wish I can take you out for a drink.
    I hope the moderators of this site knows that you are an asset.
    Thanks again.
    GiftX.

  22. #22
    New Member
    Join Date
    Aug 2016
    Posts
    1

    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

  23. #23
    Junior Member
    Join Date
    May 2010
    Posts
    25

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width