Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    444
    Thanks
    47
    Thanked 5 Times in 5 Posts

    Repeating Rows without Freezing In an HTML Export

    (Updated the title to make it more explanatory)

    Using Excel 2013 on Windows 8.1.

    I have a worksheet with 10 rows for headers.

    It is bigger than the typical screen.

    I sort the rows of this worksheet , then export it to HTML for public viewing. I have this coded in VBA, which takes say the block of rows from 11 to 99, sorts them on the basis of a column, and then exports an HTML of rows 1 to 100 so the headers are included. It would be great if I could get this into VBA too.

    I am looking for a reasonable way to have those 10 header rows repeated, say, every 20 rows so that the headers appear in rows 1-10, 31-40, 61-70, and so on. This way the downstream user interested in the bottom of the HTML does not need to constantly scroll up to see the headers.

    This is very similar to repeating some header rows across the top of a hard copy. I am thinking that it can't work the same way though, since viewing HTML in a browser isn't WYSIWYG with respect to the rows that actually appear on the screen.

    I am also OK with repeating the header rows in the original spreadsheet. If they could be locked in those rows, and left unsorted, that would work too (and be a new trick I've never seen).

    An example is attached.
    Last edited by boobounder; 2018-01-11 at 15:48. Reason: Title didn't seem very self-explantory when I viewed the forum

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,131
    Thanks
    172
    Thanked 825 Times in 753 Posts
    Hi Boo,

    Didn't see an attached sample file so made up some bogus data for testing.

    The following code will copy your data to an html file while inserting your 10 column header every 20 rows starting at row 11. The original file does not get altered with additional headers. Add your code for the sorting immediately after the Declare and set variable section. You will need to change the columns (cols) to the number of columns and change the destination path for the saved file. You can overwrite an existing html file with no errors or alerts if you run the code multiple times.

    Code:
    Sub Write2html()
    '==============================================
    'COPIES DATA TO HTML FILES AND INSERTS
    'HEADER EVERY 20 ROWS STARTING AT ROW 11
    '==============================================
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        On Error Resume Next
    '----------------------------------------------
    'DECLARE AND SET VARIABLES
        Dim wb As Workbook, header As Range
        Dim cols As Long, LastRow As Long
        Dim I As Long, NextRow As Long
        cols = 10
        LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        Set header = Range(Cells(1, 1), Cells(10, cols))
    '----------------------------------------------
    'OPEN NEW DESTINATION HTML WORKBOOK
        Set wb = Application.Workbooks.Add
        ThisWorkbook.Activate
        With wb.Worksheets(1)
    '----------------------------------------------
    'WRITE HEADER (10 ROWS)
        GoSub WriteHeader
    '----------------------------------------------
    'WRITE DATA (20 ROWS)
        For I = 11 To LastRow
            NextRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
            Range(Cells(I, 1), Cells(I, cols)).Copy .Cells(NextRow, 1)
    '----------------------------------------------
    'WRITE HEADER (10 ROWS)
            x = I Mod 20
            If x = 10 Then GoSub WriteHeader
        Next I
    '----------------------------------------------
    'SAVE AND CLOSE FILE
        wb.SaveAs Filename:="C:\Users\Maudibe\Desktop\Book1.html", FileFormat:=xlHtml 
        wb.Close
        End With
    '----------------------------------------------
    'CLEANUP
        Set wb = Nothing
        Set header = Nothing
        Application.CutCopyMode = False
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        Exit Sub
    '----------------------------------------------
    'SUBROUTINE TO WRITE HEADER
    WriteHeader:
        NextRow = wb.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1
        If NextRow = 2 Then NextRow = 1
        header.Copy wb.Worksheets(1).Cells(NextRow, 1)
    Return
    End Sub
    html.png

    2html.xlsm
    Last edited by Maudibe; 2018-01-11 at 19:35.

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    boobounder (2018-01-12)

  4. #3
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    444
    Thanks
    47
    Thanked 5 Times in 5 Posts

    A Good Start

    I have attached an actual file similar to what I might use in practice, with a modified version of your code in Module 1. Here are some issues I have.

    1) LastRow seems to be looking at column A. Is there a way to get it to look across the columns for other rows further down?
    2) The number of columns is flexible. I set it for a large number. But it would be nice to have code for that too, which searches down rows for the last column.
    3) I'm ultimately going to want this to run across multiple sheets with different numbers of rows and columns.

    So, it doesn't work right now, but I see where you're going with most of it. But I still need help.

    4) I am wondering if the outputted file looks goofy because your code is not ready to handle 2 or more sheets?
    5) I am not too concerned about the colors, but the fact that they don't match (to me) suggests something very strange going on.
    6) Column widths in the output are clearly a problem. I wonder if that is because my routine used .Publish and yours uses .Copy?

    My workflow is to run Sort, then exportToHTML, then Desort. Occasionally I need to stop after one of those and poke around, so it's easier to just keep them as separate macros. I have just inserted yours into the middle, so I run Sort, then Write2html, then Desort. It runs and generates no errors, but the output is a mess.
    Attached Files Attached Files

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,131
    Thanks
    172
    Thanked 825 Times in 753 Posts
    Boo,

    See if this nudges you a little closer.

    html1.png

    Test_Grades_Rev1.xlsm

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    boobounder (2018-01-12)

  7. #5
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    444
    Thanks
    47
    Thanked 5 Times in 5 Posts
    I think this is awesome. I will look for issues, but it looks perfect at first glance (now I have to figure out how you did it all).

  8. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,131
    Thanks
    172
    Thanked 825 Times in 753 Posts
    I have completed the build where both sheets are now converted into the html file. I have also incorporated calls to your sort/desort subroutines from the Write2html() routine. Like the previous file, the number of row and columns will automatically adjust to the size of your data.

    Let me know if I can be of more help.

    Maud

    Test_Grades_Rev4.xlsm
    Last edited by Maudibe; 2018-01-13 at 09:47.

Posting Permissions

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