VBA excel, strange behavior when converting range to html

Question

I've been trying my hand at some code that is supposed to convert a range in a worksheet to an html table. It seems to work fine mostly. But sometimes populates the rows multiple times, meaning there would be only 2 rows to copy in the active sheet, but the html table output contains repetitions of the header and data rows in the table rows of the html code. The funny thing is if I set a break point just after counting rows and columns the bug seems to occur less often. I'm really lost here could anybody shed some light on this?

I am using the following code:

 ' establish number of columns and rows to send
Report.Activate 'this is a worksheet object
NumbofRows = Report.Range("A1", Range("A1").End(xlDown)).Rows.Count
NumbofCols = Report.Range("A1", Range("A1").End(xlToRight)).Columns.Count
' Populate headers
TableHeaders = "<table> <tr>"
    For i = 1 To NumbofCols
        TableHeaders = TableHeaders & "<th>" & Report.Cells(1, i) & "</th>"
    Next i
TableHeaders = TableHeaders & "</tr>"
' populate response rows
For y = 2 To NumbofRows
    If WorksheetFunction.IsEven(y) Then
        Style = "style= " & Chr(39) & "background:#CCEBFF" & Chr(39)
    Else
        Style = "style= " & Chr(39) & "background:#E6F5FF" & Chr(39)
    End If
    ' loop through cells on the current row and add them to the table
    TableRows = TableRows & "<tr " & Style & ">"
        For x = 1 To NumbofCols
            TableRows = TableRows & "<td>" & Report.Cells(y, x) & "</td>"
        Next x
    TableRows = TableRows & "</tr>"
Next y
' close table tag
TableRows = TableRows & "</table> <br> <br>"
'stick them together
ResponseTable = TableHeaders & TableRows

There's nothing in your code which might account for what you're seeing. E.g. you only loop through the header row once, so you shouldn't see repeats of that. Maybe post some of the problem HTML if there are any clues in that... — Tim Williams
– Tim Williams, Commented Jan 23, 2015 at 22:53
Thanks for taking the time to comment, I'll grab some of the HTML and paste it on Monday. — knw
– knw, Commented Jan 24, 2015 at 12:51

user4039065 · Accepted Answer · 2015-01-24 01:33:12Z

1

A common error when relying upon ActiveSheet is to specify a cell range's parent while failing to specify the same parent on the cells that mark the start and stop of the range. Example:

NumbofRows = Report.Range("A1", Report.Range("A1").End(xlDown)).Rows.Count
NumbofCols = Report.Range("A1", Report.Range("A1").End(xlToRight)).Columns.Count

I would wrap this whole segment of code into a With ... End With code block and prefix each cell/range definition with a .. The . specifies that each reference belongs to the parent defined with the With ... End With code.

With Report
     ' establish number of columns and rows to send
    '.Activate 'NOT NECESSARY
    NumbofRows = .Range("A1", .Range("A1").End(xlDown)).Rows.Count
    NumbofCols = .Range("A1", .Range("A1").End(xlToRight)).Columns.Count

    ' Populate headers
    TableHeaders = "<table> <tr>"
    For i = 1 To NumbofCols
        TableHeaders = TableHeaders & "<th>" & .Cells(1, i) & "</th>"
    Next i
    TableHeaders = TableHeaders & "</tr>"

    ' populate response rows
    For y = 2 To NumbofRows
        If CBool(y Mod 2) Then
            Style = "style= " & Chr(39) & "background:#E6F5FF" & Chr(39)
        Else
            Style = "style= " & Chr(39) & "background:#CCEBFF" & Chr(39)
        End If
        ' loop through cells on the current row and add them to the table
        TableRows = TableRows & "<tr " & Style & ">"
        For x = 1 To NumbofCols
            TableRows = TableRows & "<td>" & .Cells(y, x) & "</td>"
        Next x
        TableRows = TableRows & "</tr>"
    Next y
    ' close table tag
    TableRows = TableRows & "</table> <br/> <br/>"
    'stick them together
    ResponseTable = TableHeaders & TableRows
End With

That should take care of errant behavior and free you from relying upon ActiveSheet for the parentage of cell/range references.

Your use of Report.Range("A1").End(xlDown) to define the extent of a range is a bit troublesome. With no rows in the table beyond a header row, you are defining all rows to the bottom of the worksheet.

answered Jan 24, 2015 at 1:33

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

knw Over a year ago

Hi Jeeped, thank you for your time to answer this, I'll need to take it back and apply it to see if it will work but I'll certainly get back to you. In my application there will never be a scenario when only the header row is present because before copy/pasting this range into the reportsheet I check for it.

knw Over a year ago

Hello Jeeped, I tested your suggestion, but it didn't quite do the trick. (Even though I did take your it to hart and have rewritten most of the code with 'with object" blocks, which is apparently also faster - thanks to you I've learned about object caching!) I suspected it might have had something to do with latent formatting from the source file so I am now also using pastespecial to past only values into the report sheet. That seems to produce the expected results so far. (not 100% sure yet since the problem occurs intermittently), so for now I'll consider this answered. Thanks!

Collectives™ on Stack Overflow

VBA excel, strange behavior when converting range to html

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related