CODEWITHSUNDEEP
htmlvbaexcelrange
knw
knw

Reputation: 23

VBA excel, strange behavior when converting range to html

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

Upvotes: 0

Views: 809

Answers (1)

user4039065
user4039065

Reputation:

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.

Upvotes: 1

Related Questions