VBA – Export of selected cells to HTML

Posted on Posted in VBA \ Excel

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>"

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

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.