Monday, 22 January 2018

VBA - Excel table to HTML

Just a little routine to help me write these articles. Given an Excel range it will generate the HTML for a table with some subtle (i.e grey) styling.

Option Explicit

Function MarkupTable(ByVal rng As Excel.Range)

    Dim s As String
    s = "<table style='border: 1px solid lightgrey;'>"
    
    Dim rngRowLoop As Excel.Range
    For Each rngRowLoop In rng.Rows
        s = s & "<tr>"
    
        Dim rngCellLoop As Excel.Range
        For Each rngCellLoop In rngRowLoop.Cells
        
            s = s & "<td style='border: 1px solid lightgrey;'>" & rngCellLoop.Value2 & "</td>"
        
        Next rngCellLoop
    
    
        s = s & "</tr>"
    Next
    

    s = s & "</table>"

    MarkupTable = s

End Function

Sub TestMarkupTable()
    Debug.Print MarkupTable(ActiveCell.CurrentRegion)
End Sub

sample

0018095CIAccessibleStatus BarMsoCommandBarStatus Bar
003400F0IAccessibleRibbonMsoCommandBarRibbon
00130E1AITextDocument2CalibriRICHEDIT60WFont Selector?
00140D90ITextDocument211RICHEDIT60WFont size selector?
000E0F54ITextDocument2GeneralRICHEDIT60WFormat selector?
000E034CWindowBook2EXCEL7TrueA window on a workbook

No comments:

Post a Comment