Click here to Skip to main content
13,192,426 members (44,752 online)
Click here to Skip to main content
Add your own
alternative version


19 bookmarked
Posted 24 Aug 2006

Excel to HTML Macro

, 24 Aug 2006
Rate this:
Please Sign up or sign in to vote.
A simple macro that will convert your Excel data into a clean HTML table.

Sample Image - exportHTML.gif


This macro is designed to take the highlighted cells of an Excel spreadsheet and create a simple HTML table to display the output.


I wrote this out of necessity for my job because I was unable to find code that did what I needed. What differentiates my approach from the other Excel to HTML converters I have seen is that many others take the approach of making the generated web page look as much like the spreadsheet as possible. I wanted this to make it easy to create a web page that looked like it belonged on my website not in MS Office.

For me, this means that it is flexible enough to be able to insert my stylesheet information and other properties and not have any of the garbage HTML that Microsoft likes to put in their pages. Also, I only cared about the output of the cell, not any underlying formula that generated the text. In other words, I wanted simple clean static HTML.

Using the code

There are a couple of ways to install the code. The simplest is to copy it to the C:\Documents and Settings\<USER>\Application Data\Microsoft\Excel\XLSTART directory. This will make the macro available by opening the file anytime Excel opens, by clicking on Tools > Macro > macros, and selecting the exportHTML macro. A much nicer way is to convert it to an add-in and then assign the macro to a custom button. Go to the Microsoft website to see how to do this for your version of Office.

The basic idea is that the macro writes a string of HTML based on the selection of cells. Any user input by way of the form is incorporated into the HTML. Any style information inserted into the rows or columns will be inserted into every row or column generated by the script. Also, the default behavior is to copy the HTML to the clipboard. You have the ability to write the HTML to a file by going to the Options tab and selecting the file. This will overwrite the file.

Here is the code. It makes more sense when you are looking at the form and its properties.

Private Sub cellWidth_Change()
    If cellWidth.Value = True Then table100pct.Value = False
End Sub

Private Sub findFile_Click()
   ' Requires reference to Microsoft Office 11.0 Object Library.
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant

   ' Clear listbox contents.
   'Me.FileList.RowSource = ""

   ' Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

   With fDialog

      'Do not allow user to make multiple selections in dialog box
      .AllowMultiSelect = False
      ' Set the title of the dialog box.
      .Title = "Please select a file"

      ' Clear out the current filters, and add our own.
      .Filters.Add "All Files", "*.*"
      .Filters.Add "ASP files", "*.asp"
      .Filters.Add ".Net files", "*.aspx"
      .Filters.Add "Html files", "*.htm, *.html"

      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then

         'Loop through each file selected and add it to our list box.
         For Each varFile In .SelectedItems
            filePath.Text = varFile
         'MsgBox .SelectedItems.Item(0)
      End If
   End With
End Sub

Private Sub makeHTML_Click()
    Dim DestFile As String
    Dim htmlOut As String
    Dim FileNum As Integer
    Dim ColumnCount As Integer
    Dim RowCount As Integer
    Dim vbTableWith As String
    Dim vbTableFStyle As String
    Dim vbCellWith As String
    Dim vbCellBGColor As String
    Dim vbCellFStyle As String
    Dim vbFontColor As String
    Dim vbBold As String
    Dim vbItalic As String
    Dim outputObj As New DataObject
    'if style or class information is used write 
    'it to the sorresponding string variable
    If Trim(tableStyle.Text) <> "" Then
        vbTableStyle = " style='" & tableStyle.Text & "' "
        vbTableStyle = ""
    If Trim(tableClass.Text) <> "" Then
        vbTableClass = " class='" & tableClass.Text & "' "
        vbTableClass = ""
    If Trim(tableId.Text) <> "" Then
        vbTableId = " id='" & tableId.Text & "' "
        vbTableId = ""
    If Trim(rowStyle.Text) <> "" Then
        vbRowStyle = " style='" & rowStyle.Text & "' "
        vbRowStyle = ""
    If Trim(rowClass.Text) <> "" Then
        vbRowClass = " class='" & rowClass.Text & "' "
        vbRowClass = ""
    If Trim(cellStyle.Text) <> "" Then
       vbCellStyle = " style='" & cellStyle.Text & "' "
        vbCellStyle = ""
    If Trim(cellClass.Text) <> "" Then
        vbCellClass = " class='" & cellClass.Text & "' "
        vbCellClass = ""
    'used for specific width
    If cellWidth = True Then
        vbTableWidth = " width:" & Selection.Columns.Width & "; "
    End If
    'stretch table to 100%
    If table100pct = True Then
        vbTableWidth = "  width:100%; "
    End If
    vbTableFStyle = " style='" & vbTableWidth & "' "
    'Write the table
    htmlOut = "<table cellpadding=0 cellspacing=0 border=0 " & _
              vbTableId & vbTableStyle & vbTableClass & _
              vbTableFStyle & ">" & vbCrLf
    ' Loop for each row in selection.
    For RowCount = 1 To Selection.Rows.Count
      ' Loop for each column in selection.
      htmlOut = htmlOut & "<tr" & vbRowClass & vbRowStyle & ">" & vbCrLf
      For ColumnCount = 1 To Selection.Columns.Count
            'if the width is fixed then preserve width of each cell
            If cellWidth = True Then
                vbCellWidth = " width:" & _
                     Selection.Cells(RowCount, ColumnCount).Width & "; "
                vbCellWith = ""
            End If
            'if checked use font color
            If useFontColor = True Then
                vbFontColor = " color: " & _
                index2Hex(Selection.Cells(RowCount, _
                ColumnCount).Font.colorIndex) & "; "
                vbFontColor = ""
            End If
            'if checked use background color
            If useBGColor = True Then
                vbCellBGColor = " background: " & _
                index2Hex(Selection.Cells(RowCount, _
                ColumnCount).Interior.colorIndex) & "; "
                vbCellBGColor = ""
            End If
            'if checked use Bold
            If useBold = True Then
                If Selection.Cells(RowCount, _
                             ColumnCount).Font.Bold = True Then
                    vbBold = " font-weight: bold; "
                End If
                vbBold = ""
            End If
            'if checked use italic
            If useItalic = True Then
                If Selection.Cells(RowCount, _
                             ColumnCount).Font.Italic = True Then
                    vbItalic = " font-style: italic; "
                End If
                vbItalic = ""
            End If
                vbCellFStyle = " style='" & vbFontColor & vbCellWidth _
                               & vbCellBGColor & vbBold & vbItalic & "' "
         ' Write current cell's text
         htmlOut = htmlOut & "<td" & vbCellClass & vbCellStyle _
                 & vbCellFStyle & ">" & Selection.Cells(RowCount, _
            ColumnCount).Text & "</td>"
         ' Check if cell is in last column.
         If ColumnCount = Selection.Columns.Count Then
            ' If so, then write a blank line.
            htmlOut = htmlOut & vbCrLf
         End If
      ' Start next iteration of ColumnCount loop.
      Next ColumnCount
    ' Start next iteration of RowCount loop.
    htmlOut = htmlOut & "</tr>" & vbCrLf
    Next RowCount
    htmlOut = htmlOut & "</table>" & vbCrLf
    'force rendering of empty cells
    If emptyCell = True Then htmlOut = Replace(htmlOut, "></td>", ">&nbsp;</td>")

    'Writing HTML to file if checked
    If Trim(filePath.Text) <> "" Then
        DestFile = filePath.Text
        ' Obtain next free file handle number.
        FileNum = FreeFile()
        ' Turn error checking off.
        On Error Resume Next
        ' Attempt to open destination file for output.
        Open DestFile For Output As #FileNum
        ' If an error occurs report it and end.
        If Err <> 0 Then
          MsgBox Err.Description
          MsgBox "Cannot open filename " & DestFile
            Print #FileNum, htmlOut;
            ' Close destination file.
            Close #FileNum
        End If
    End If
    ' Turn error checking on.
    On Error GoTo 0
    'if checked copy HTML to clipboard
    If copyClipboard.Value = True Then
        outputObj.SetText (htmlOut)
    End If
End Sub

Private Sub table100pct_Change()
    If table100pct.Value = True Then cellWidth.Value = False
End Sub

'a lookup table to convert a ColorIndex value
'to its Hex equivilant
Private Function index2Hex(index)
    Dim hexColor As String
    Dim colorTable(56) As String
    colorTable(1) = "#000000"
    colorTable(2) = "#FFFFFF"
    colorTable(3) = "#FF0000"
    colorTable(4) = "#00FF00"
    colorTable(5) = "#0000FF"
    colorTable(6) = "#FFFF00"
    colorTable(7) = "#FF00FF"
    colorTable(8) = "#00FFFF"
    colorTable(9) = "#800000"
    colorTable(10) = "#008000"
    colorTable(11) = "#000080"
    colorTable(12) = "#808000"
    colorTable(13) = "#800080"
    colorTable(14) = "#008080"
    colorTable(15) = "#C0C0C0"
    colorTable(16) = "#808080"
    colorTable(17) = "#9999FF"
    colorTable(18) = "#993366"
    colorTable(19) = "#FFFFCC"
    colorTable(20) = "#CCFFFF"
    colorTable(21) = "#660066"
    colorTable(22) = "#FF8080"
    colorTable(23) = "#0066CC"
    colorTable(24) = "#CCCCFF"
    colorTable(25) = "#000080"
    colorTable(26) = "#FF00FF"
    colorTable(27) = "#FFFF00"
    colorTable(28) = "#00FFFF"
    colorTable(29) = "#800080"
    colorTable(30) = "#800000"
    colorTable(31) = "#008080"
    colorTable(32) = "#0000FF"
    colorTable(33) = "#00CCFF"
    colorTable(34) = "#CCFFFF"
    colorTable(35) = "#CCFFCC"
    colorTable(36) = "#FFFF99"
    colorTable(37) = "#99CCFF"
    colorTable(38) = "#FF99CC"
    colorTable(39) = "#CC99FF"
    colorTable(40) = "#FFCC99"
    colorTable(41) = "#3366FF"
    colorTable(42) = "#33CCCC"
    colorTable(43) = "#99CC00"
    colorTable(44) = "#FFCC00"
    colorTable(45) = "#FF9900"
    colorTable(46) = "#FF6600"
    colorTable(47) = "#666699"
    colorTable(48) = "#969696"
    colorTable(49) = "#003366"
    colorTable(50) = "#339966"
    colorTable(51) = "#003300"
    colorTable(52) = "#333300"
    colorTable(53) = "#993300"
    colorTable(54) = "#993366"
    colorTable(55) = "#333399"
    colorTable(56) = "#333333"
    If index = xlColorIndexNone Then index = 2
    If index = xlColorIndexAutomatic Then index = 1
    hexColor = colorTable(index)
    index2Hex = hexColor
End Function

Points of interest

This was an amazingly simple piece of code that I had a lot of fun making. It has proved immensely valuable to me as a web developer. I haven’t tested it on older Office versions, but I think any changes would be simple to implement. I hope I have time to come back and make improvements to the code, but if you beat me to it, drop me a line and let me see what you have done with it. Some suggestions are to:

  • Automate the script to run on all Excel files inside of a folder or all worksheets in a file.
  • Be able to preview and change style information on individual cells or rows prior to writing the final HTML.
  • Colspans and rowspans.
  • Any DHTML behaivior you can think of.
  • A simple change would be to optionally alternate the background color of rows in the table.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Web Developer
United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralPerfect! Pin
cruzan124-Sep-09 17:23
membercruzan124-Sep-09 17:23 
GeneralError message Pin
kx3217-Sep-09 7:57
memberkx3217-Sep-09 7:57 
GeneralMerge cell Pin
alepompier23-Oct-07 0:16
memberalepompier23-Oct-07 0:16 
GeneralO.o Pin
merwatson6-Sep-07 10:06
membermerwatson6-Sep-07 10:06 
GeneralRe: O.o Pin
| kal9091@yahoo.com6-Sep-07 10:27
member| kal9091@yahoo.com6-Sep-07 10:27 
QuestionFurther development Pin
ozmariners27-Sep-06 14:55
memberozmariners27-Sep-06 14:55 
AnswerRe: Further development Pin
| kal9091@yahoo.com28-Sep-06 11:58
member| kal9091@yahoo.com28-Sep-06 11:58 
GeneralRe: Further development Pin
N i c c k28-Sep-06 20:58
memberN i c c k28-Sep-06 20:58 
GeneralRe: Further development Pin
| kal9091@yahoo.com13-Oct-06 3:26
member| kal9091@yahoo.com13-Oct-06 3:26 
Hi, Can you give me some more information on your problem? what is your input and what are you receiving for your output and what version of excel are you working on. This was a side project to help me out with a much larger project at my office and I only had Excel 2003 to work with. It might be quirky with other versions I have know idea but if you explain your problem more and give some examples maybe someone else or myself can help you

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171017.2 | Last Updated 24 Aug 2006
Article Copyright 2006 by |
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid