VBA – Export of selected cells to HTML

Posted on August 13, 2013 by

Hello! In this article I will present a code for the easy export of selected cells into a HTML file.

In general, the code functions in the following way:

1. Takes a look at the selected range and performs a check whether there are any values in it. If there are no values, it returns a messagebox with message “Nothing to Export”.

2. As a second step the code initiates file save. The default name is “vitoshacademy.htm”

3. Here comes the interesting part => The genration of the code into the htm file which we have just created in step 2. In order to do this, we use the following 3 lines:

Open Filename For Output As #1
Print #1, "<HTML>"
Print #1, "<TABLE BORDER=1 CELLPADDING=3>"

Open Filename For Output As #1

Print #1, "<HTML>"

Print #1, "<TABLE BORDER=1 CELLPADDING=3>"

Filename is the variable, which keeps the saved file.

4. The tricky part contains itself in the question – how do we generate a html code from a table? And here comes the answer – with a loop and a few conditional statements.

    For r = 1 To Rng.Rows.Count
        Print #1, "<TR>"
        For c = 1 To Rng.Columns.Count
            Select Case Rng.Cells(r, c).HorizontalAlignment
                Case xlHAlignLeft
                    TDOpenTag = "<TD ALIGN=LEFT>"
                Case xlHAlignCenter
                    TDOpenTag = "<TD ALIGN=CENTER>"
                Case xlHAlignGeneral
                    If IsNumeric(Rng.Cells(r, c)) Then
                      TDOpenTag = "<TD ALIGN=RIGHT>"
                    Else
                      TDOpenTag = "<TD ALIGN=LEFT>"
                    End If
                Case xlHAlignRight
                    TDOpenTag = "<TD ALIGN=RIGHT>"
            End Select

            TDCloseTag = "</TD>"
            If Rng.Cells(r, c).Font.Bold Then
                TDOpenTag = TDOpenTag & "<B>"
                TDCloseTag = "</B>" & TDCloseTag
            End If
            If Rng.Cells(r, c).Font.Italic Then
                TDOpenTag = TDOpenTag & "<I>"
                TDCloseTag = "</I>" & TDCloseTag
            End If
            CellContents = Rng.Cells(r, c).Text
            Print #1, TDOpenTag & CellContents & TDCloseTag
        Next c
        Print #1, "</TR>"
    Next r

For r = 1 To Rng.Rows.Count

Print #1, "<TR>"

For c = 1 To Rng.Columns.Count

Select Case Rng.Cells(r, c).HorizontalAlignment

Case xlHAlignLeft

TDOpenTag = "<TD ALIGN=LEFT>"

Case xlHAlignCenter

TDOpenTag = "<TD ALIGN=CENTER>"

Case xlHAlignGeneral

If IsNumeric(Rng.Cells(r, c)) Then

TDOpenTag = "<TD ALIGN=RIGHT>"

Else

TDOpenTag = "<TD ALIGN=LEFT>"

End If

Case xlHAlignRight

TDOpenTag = "<TD ALIGN=RIGHT>"

End Select

TDCloseTag = "</TD>"

If Rng.Cells(r, c).Font.Bold Then

TDOpenTag = TDOpenTag & "<B>"

TDCloseTag = "</B>" & TDCloseTag

End If

If Rng.Cells(r, c).Font.Italic Then

TDOpenTag = TDOpenTag & "<I>"

TDCloseTag = "</I>" & TDCloseTag

End If

CellContents = Rng.Cells(r, c).Text

Print #1, TDOpenTag & CellContents & TDCloseTag

Next c

Print #1, "</TR>"

Next r

5. At the end we close the HTML tags and the file, the same way as we have opened them.

6. In order to inform the user, that this has been done, we call a messagebox with information about the path, where the code is.

7. So at the end you just have to go to the path and open the newly generated file.

Here is the original excel file.