none
Create HTML file from excel sheet using vba RRS feed

  • Question

  • Hi everyone,

    I have some data in my excel sheet(Sheet2) and I need to export those data in a HTML file. I have 2 columns in my excel sheet i.e. Column A- ID, Column B- Name. Column header is present in 2nd row and actual data is starting from 3rd row i.e. A3 cell.

    I want a macro which will export all the data present in Sheet2 starting from A3 cell to a HTML file in a tabular format.

    I am already using the following code to convert an excel sheet to HTML format. But the problem is, it is converting the whole workbook into HTML which I don't want. I want to export only Column A & B of Sheet2 in a HTML file.

    Sub export
    Dim location As String
    location = Application.ActiveWorkbook.path & "\Book1.htm"
    ActiveWorkbook.SaveAs Filename:=location, FileFormat:=xlHtml
    End Sub
    

    Wednesday, July 1, 2015 6:31 PM

Answers

  • You could try code like this - you don't need all of it after the "End With" line - the rest is just to show the resulting file.

    Sub MakeHTMLTableFile()
        Dim myFileName As String
        Dim myHTML As String
        Dim FileNum As Integer
        Dim myC As Integer
        Dim myR As Long
        Dim retVal As Variant
        
        'Create filename
        myFileName = ThisWorkbook.Path & "\Table File.html"
        
        With Worksheets("Sheet2")
            
            'Create the string that is the file contents
            myHTML = "<HTML>"
            myHTML = myHTML & Chr(10) & "<HEAD>"
            myHTML = myHTML & Chr(10) & "<META HTTP-EQUIV=""Content-Type"" CONTENT=""text/html; charset=windows-1252"">"
            myHTML = myHTML & Chr(10) & "<META NAME=""Generator"" CONTENT=""Microsoft Word 97"">"
            myHTML = myHTML & Chr(10) & "</HEAD>"
            myHTML = myHTML & Chr(10) & "<TABLE BORDER=""5"">"
            myHTML = myHTML & Chr(10) & "<TR>"
            myHTML = myHTML & Chr(10) & "<TH COLSPAN=""2"">"
            myHTML = myHTML & Chr(10) & "<H3><BR>TABLE TITLE</H3></TH>"
            myHTML = myHTML & Chr(10) & "</TR>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 1).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 2).Value & "</TH>"
            
            For myR = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
                myHTML = myHTML & "<TR><TD>" & .Cells(myR, 1).Value & "</TD><TD>" & .Cells(myR, 2).Value & "</TD></TR>"
            Next myR
            myHTML = myHTML & Chr(10) & "</TABLE><br>"
            myHTML = myHTML & Chr(10) & "<FONT SIZE=2></FONT></BODY>"
            myHTML = myHTML & Chr(10) & "</HTML>"
            myHTML = Replace(myHTML, Chr(10), "</p><p>")
            On Error GoTo ErrHandler
            FileNum = FreeFile    ' next free filenumber
            Open myFileName For Output As #FileNum    ' creates the new file
            Print #FileNum, myHTML
            Close #FileNum    ' close the file
        End With
        MsgBox "File " & myFileName & " was created."
        
        retVal = Shell("C:\Program Files\Internet Explorer" _
        & "\IEXPLORE.EXE " & myFileName, vbNormalFocus)
        
        Exit Sub
        
    ErrHandler:
        MsgBox "Some sort of error occurred while creating file."
        
    End Sub

    • Marked as answer by Ed_Dao Tuesday, July 7, 2015 6:01 AM
    Wednesday, July 1, 2015 8:33 PM
  • Sorry, missed that part:

    Sub TwoTablesSideBySide()
        Dim myFileName As String
        Dim myHTML As String
        Dim FileNum As Integer
        Dim myC As Integer
        Dim myR As Long
        Dim retVal As Variant
         Dim b As Long
        

        myFileName = ThisWorkbook.Path & "\Test.html"
        
        With Worksheets("Sheet2")
            b = .Cells(.Rows.Count, "A").End(xlUp).Row - 2
            .Range("H8").Formula = "=COUNTIF(C:C,""XX"")"
            .Range("H9").Formula = "=COUNTIF(C:C,""YY"")"
           
            myHTML = "<HTML>"
            myHTML = myHTML & Chr(10) & "<HEAD>"
            myHTML = myHTML & Chr(10) & "<META HTTP-EQUIV=""Content-Type"" CONTENT=""text/html; charset=windows-1252"">"
            myHTML = myHTML & Chr(10) & "<META NAME=""Generator"" CONTENT=""Microsoft Word 97"">"
            myHTML = myHTML & Chr(10) & "</HEAD>"
            myHTML = myHTML & Chr(10) & "<TABLE BORDER=""5"" style=""float: left"">"
            myHTML = myHTML & Chr(10) & "<TR>"
            myHTML = myHTML & Chr(10) & "<TH COLSPAN=""4"">"
            myHTML = myHTML & Chr(10) & "<H3><BR>Test Log</H3></TH>"
            myHTML = myHTML & Chr(10) & "</TR>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 1).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 2).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 3).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 4).Value & "</TH>"
            
            For myR = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
                
                myHTML = myHTML & "<TR><TD>" & .Cells(myR, 1).Value & "</TD>" & _
                                        "<TD>" & .Cells(myR, 2).Value & "</TD>" & _
                                        "<TD>" & .Cells(myR, 3).Value & "</TD>" & _
                                        "<TD>" & .Cells(myR, 4).Value & "</TD></TR>"
            Next myR
            myHTML = myHTML & Chr(10) & "</TABLE>"
            myHTML = myHTML & Chr(10) & "<TABLE BORDER=""5"" style=""float: left"">"
            myHTML = myHTML & Chr(10) & "<TH COLSPAN=""2""><H3><BR>Status</H3></TH>"
            myHTML = myHTML & "<TR><TD>" & "Total Point" & "</TD><TD>" & b & "</TD></TR>"
            myHTML = myHTML & "<TR><TD>" & "Total XX" & "</TD><TD>" & .Cells(8, 8).Value & "</TD></TR>"
            myHTML = myHTML & "<TR><TD>" & "Total YY" & "</TD><TD>" & .Cells(9, 8).Value & "</TD></TR>"
            myHTML = myHTML & Chr(10) & "</TABLE><br>"
            myHTML = myHTML & Chr(10) & "<FONT SIZE=2></FONT></BODY>"
            myHTML = myHTML & Chr(10) & "</HTML>"
            myHTML = Replace(myHTML, Chr(10), "</p><p>")
             myHTML = myHTML & Chr(10) & "</HTML>"
            myHTML = Replace(myHTML, Chr(10), "</p><p>")
            On Error GoTo ErrHandler
            FileNum = FreeFile
            Open myFileName For Output As #FileNum
            Print #FileNum, myHTML
            Close #FileNum
        End With
        MsgBox "File " & myFileName & " was created."
        
        retVal = Shell("C:\Program Files\Internet Explorer" _
        & "\IEXPLORE.EXE " & myFileName, vbNormalFocus)
        
        Exit Sub
        
    ErrHandler:
        MsgBox "Some sort of error occurred while creating file."
    End Sub

    • Marked as answer by Ed_Dao Tuesday, July 7, 2015 6:01 AM
    Monday, July 6, 2015 7:49 PM
  • Here's some space between the two:

    Sub TwoTablesSideBySide()
        Dim myFileName As String
        Dim myHTML As String
        Dim FileNum As Integer
        Dim myC As Integer
        Dim myR As Long
        Dim retVal As Variant
         Dim b As Long
        

        myFileName = ThisWorkbook.Path & "\Test.html"
        
        With Worksheets("Sheet2")
            b = .Cells(.Rows.Count, "A").End(xlUp).Row - 2
            .Range("H8").Formula = "=COUNTIF(C:C,""XX"")"
            .Range("H9").Formula = "=COUNTIF(C:C,""YY"")"
           
            myHTML = "<HTML>"
            myHTML = myHTML & Chr(10) & "<HEAD>"
            myHTML = myHTML & Chr(10) & "<META HTTP-EQUIV=""Content-Type"" CONTENT=""text/html; charset=windows-1252"">"
            myHTML = myHTML & Chr(10) & "<META NAME=""Generator"" CONTENT=""Microsoft Word 97"">"
            myHTML = myHTML & Chr(10) & "</HEAD>"
            myHTML = myHTML & Chr(10) & "<TABLE style=""MARGIN: 0px 100px"" BORDER=""5"" style=""float: left"">"
            myHTML = myHTML & Chr(10) & "<TR>"
            myHTML = myHTML & Chr(10) & "<TH COLSPAN=""4"">"
            myHTML = myHTML & Chr(10) & "<H3><BR>Test Log</H3></TH>"
            myHTML = myHTML & Chr(10) & "</TR>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 1).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 2).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 3).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 4).Value & "</TH>"
            
            For myR = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
                
                myHTML = myHTML & "<TR><TD>" & .Cells(myR, 1).Value & "</TD>" & _
                                        "<TD>" & .Cells(myR, 2).Value & "</TD>" & _
                                        "<TD>" & .Cells(myR, 3).Value & "</TD>" & _
                                        "<TD>" & .Cells(myR, 4).Value & "</TD></TR>"
            Next myR
            myHTML = myHTML & Chr(10) & "</TABLE>"
            myHTML = myHTML & Chr(10) & "<TABLE BORDER=""5"" HSPACE=""50"" style=""float: left"">"   ' style=""float: left""
            myHTML = myHTML & Chr(10) & "<TH COLSPAN=""2""><H3><BR>Status</H3></TH>"
            myHTML = myHTML & "<TR><TD>" & "Total Point" & "</TD><TD>" & b & "</TD></TR>"
            myHTML = myHTML & "<TR><TD>" & "Total XX" & "</TD><TD>" & .Cells(8, 8).Value & "</TD></TR>"
            myHTML = myHTML & "<TR><TD>" & "Total YY" & "</TD><TD>" & .Cells(9, 8).Value & "</TD></TR>"
            myHTML = myHTML & Chr(10) & "</TABLE><br>"
            myHTML = myHTML & Chr(10) & "<FONT SIZE=2></FONT></BODY>"
            myHTML = myHTML & Chr(10) & "</HTML>"
            myHTML = Replace(myHTML, Chr(10), "</p><p>")
             myHTML = myHTML & Chr(10) & "</HTML>"
            myHTML = Replace(myHTML, Chr(10), "</p><p>")
            On Error GoTo ErrHandler
            FileNum = FreeFile
            Open myFileName For Output As #FileNum
            Print #FileNum, myHTML
            Close #FileNum
        End With
        MsgBox "File " & myFileName & " was created."
        
        retVal = Shell("C:\Program Files\Internet Explorer" _
        & "\IEXPLORE.EXE " & myFileName, vbNormalFocus)
        
        Exit Sub
        
    ErrHandler:
        MsgBox "Some sort of error occurred while creating file."
    End Sub

    • Marked as answer by Ed_Dao Tuesday, July 7, 2015 5:17 PM
    Tuesday, July 7, 2015 1:37 PM

All replies

  • You could try code like this - you don't need all of it after the "End With" line - the rest is just to show the resulting file.

    Sub MakeHTMLTableFile()
        Dim myFileName As String
        Dim myHTML As String
        Dim FileNum As Integer
        Dim myC As Integer
        Dim myR As Long
        Dim retVal As Variant
        
        'Create filename
        myFileName = ThisWorkbook.Path & "\Table File.html"
        
        With Worksheets("Sheet2")
            
            'Create the string that is the file contents
            myHTML = "<HTML>"
            myHTML = myHTML & Chr(10) & "<HEAD>"
            myHTML = myHTML & Chr(10) & "<META HTTP-EQUIV=""Content-Type"" CONTENT=""text/html; charset=windows-1252"">"
            myHTML = myHTML & Chr(10) & "<META NAME=""Generator"" CONTENT=""Microsoft Word 97"">"
            myHTML = myHTML & Chr(10) & "</HEAD>"
            myHTML = myHTML & Chr(10) & "<TABLE BORDER=""5"">"
            myHTML = myHTML & Chr(10) & "<TR>"
            myHTML = myHTML & Chr(10) & "<TH COLSPAN=""2"">"
            myHTML = myHTML & Chr(10) & "<H3><BR>TABLE TITLE</H3></TH>"
            myHTML = myHTML & Chr(10) & "</TR>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 1).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 2).Value & "</TH>"
            
            For myR = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
                myHTML = myHTML & "<TR><TD>" & .Cells(myR, 1).Value & "</TD><TD>" & .Cells(myR, 2).Value & "</TD></TR>"
            Next myR
            myHTML = myHTML & Chr(10) & "</TABLE><br>"
            myHTML = myHTML & Chr(10) & "<FONT SIZE=2></FONT></BODY>"
            myHTML = myHTML & Chr(10) & "</HTML>"
            myHTML = Replace(myHTML, Chr(10), "</p><p>")
            On Error GoTo ErrHandler
            FileNum = FreeFile    ' next free filenumber
            Open myFileName For Output As #FileNum    ' creates the new file
            Print #FileNum, myHTML
            Close #FileNum    ' close the file
        End With
        MsgBox "File " & myFileName & " was created."
        
        retVal = Shell("C:\Program Files\Internet Explorer" _
        & "\IEXPLORE.EXE " & myFileName, vbNormalFocus)
        
        Exit Sub
        
    ErrHandler:
        MsgBox "Some sort of error occurred while creating file."
        
    End Sub

    • Marked as answer by Ed_Dao Tuesday, July 7, 2015 6:01 AM
    Wednesday, July 1, 2015 8:33 PM
  • Hi Bernie,

    Thanks much for your reply. It's working absolutely correct. However I made some changes in the code as per my need. Basically I am creating 2 tables in the HTML file. Could you please let me know how to show them side by side. Currently it's appearing one after another. Here is my modified code.

    Private Sub CommandButton1_Click()
    Dim myFileName As String
        Dim myHTML As String
        Dim FileNum As Integer
        Dim myC As Integer
        Dim myR As Long
        Dim retVal As Variant
        
    
        myFileName = ThisWorkbook.Path & "\Test.html"
        
        With Worksheets("Sheet2")
            a = Worksheets("Sheet2").Cells(Worksheets("Sheet2").Rows.Count, "A").End(xlUp).Row
            b = a - 2
           Worksheets("Sheet2").Range("H8").Formula = "=COUNTIF(C3:C9,""XX"")"
           Worksheets("Sheet2").Range("H9").Formula = "=COUNTIF(C3:C9,""YY"")"
           
            myHTML = "<HTML>"
            myHTML = myHTML & Chr(10) & "<HEAD>"
            myHTML = myHTML & Chr(10) & "<META HTTP-EQUIV=""Content-Type"" CONTENT=""text/html; charset=windows-1252"">"
            myHTML = myHTML & Chr(10) & "<META NAME=""Generator"" CONTENT=""Microsoft Word 97"">"
            myHTML = myHTML & Chr(10) & "</HEAD>"
            myHTML = myHTML & Chr(10) & "<TABLE BORDER=""5"">"
            myHTML = myHTML & Chr(10) & "<TR>"
            myHTML = myHTML & Chr(10) & "<TH COLSPAN=""4"">"
            myHTML = myHTML & Chr(10) & "<H3><BR>Test Log</H3></TH>"
            myHTML = myHTML & Chr(10) & "</TR>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 1).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 2).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 3).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 4).Value & "</TH>"
            
            For myR = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
                
                myHTML = myHTML & "<TR><TD>" & .Cells(myR, 1).Value & "</TD><TD>" & .Cells(myR, 2).Value & "</TD><TD>" & .Cells(myR, 3).Value & "</TD><TD> & .Cells(myR, 4).Value & </TD></TR>"
                
            Next myR
            myHTML = myHTML & Chr(10) & "</TABLE><br>"
            
            yHTML = yHTML & Chr(10) & "<TABLE BORDER=""5"">"
            yHTML = yHTML & Chr(10) & "<TR>"
            yHTML = yHTML & Chr(10) & "<H3><BR>Status</H3></TH>"
            yHTML = yHTML & Chr(10) & "</TR>"
            yHTML = yHTML & "<TR><TD>" & "Total Point" & "</TD><TD>" & "</TD><TD>" & b & "</TD></TR>"
            yHTML = yHTML & "<TR><TD>" & "Total XX" & "</TD><TD>" & "</TD><TD>" & .Cells(8, 8).Value & "</TD></TR>"
            yHTML = yHTML & "<TR><TD>" & "Total YY" & "</TD><TD>" & "</TD><TD>" & .Cells(9, 8).Value & "</TD></TR>"
            yHTML = yHTML & Chr(10) & "</TABLE><br>"
            myHTML = myHTML & Chr(10) & "<FONT SIZE=2></FONT></BODY>"
            myHTML = myHTML & Chr(10) & "</HTML>"
            myHTML = Replace(myHTML, Chr(10), "</p><p>")
             yHTML = yHTML & Chr(10) & "</HTML>"
            yHTML = Replace(yHTML, Chr(10), "</p><p>")
            On Error GoTo ErrHandler
            FileNum = FreeFile
            Open myFileName For Output As #FileNum
            Print #FileNum, myHTML
            Print #FileNum, yHTML
            Close #FileNum
        End With
        MsgBox "File " & myFileName & " was created."
        
        retVal = Shell("C:\Program Files\Internet Explorer" _
        & "\IEXPLORE.EXE " & myFileName, vbNormalFocus)
        
        Exit Sub
        
    ErrHandler:
        MsgBox "Some sort of error occurred while creating file."
    End Sub

    Thanks.



    • Edited by Ed_Dao Thursday, July 2, 2015 6:09 PM
    Thursday, July 2, 2015 6:06 PM
  • Hi Bernie,

    Do you have any update on this?

    Monday, July 6, 2015 8:51 AM
  • I was relaxing at the beach ;-)

    You did not match up your tag codes - try this:

    Sub TwoTables()
        Dim myFileName As String
        Dim myHTML As String
        Dim FileNum As Integer
        Dim myC As Integer
        Dim myR As Long
        Dim retVal As Variant
         Dim b As Long
        

        myFileName = ThisWorkbook.Path & "\Test.html"
        
        With Worksheets("Sheet2")
            b = .Cells(.Rows.Count, "A").End(xlUp).Row - 2
            .Range("H8").Formula = "=COUNTIF(C:C,""XX"")"
            .Range("H9").Formula = "=COUNTIF(C:C,""YY"")"
           
            myHTML = "<HTML>"
            myHTML = myHTML & Chr(10) & "<HEAD>"
            myHTML = myHTML & Chr(10) & "<META HTTP-EQUIV=""Content-Type"" CONTENT=""text/html; charset=windows-1252"">"
            myHTML = myHTML & Chr(10) & "<META NAME=""Generator"" CONTENT=""Microsoft Word 97"">"
            myHTML = myHTML & Chr(10) & "</HEAD>"
            myHTML = myHTML & Chr(10) & "<TABLE BORDER=""5"">"
            myHTML = myHTML & Chr(10) & "<TR>"
            myHTML = myHTML & Chr(10) & "<TH COLSPAN=""4"">"
            myHTML = myHTML & Chr(10) & "<H3><BR>Test Log</H3></TH>"
            myHTML = myHTML & Chr(10) & "</TR>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 1).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 2).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 3).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 4).Value & "</TH>"
            
            For myR = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
                
                myHTML = myHTML & "<TR><TD>" & .Cells(myR, 1).Value & "</TD>" & _
                                        "<TD>" & .Cells(myR, 2).Value & "</TD>" & _
                                        "<TD>" & .Cells(myR, 3).Value & "</TD>" & _
                                        "<TD>" & .Cells(myR, 4).Value & "</TD></TR>"
            Next myR
            myHTML = myHTML & Chr(10) & "</TABLE>"
            myHTML = myHTML & Chr(10) & "<TABLE BORDER=""5"">"
            myHTML = myHTML & Chr(10) & "<TH COLSPAN=""2""><H3><BR>Status</H3></TH>"
            myHTML = myHTML & "<TR><TD>" & "Total Point" & "</TD><TD>" & b & "</TD></TR>"
            myHTML = myHTML & "<TR><TD>" & "Total XX" & "</TD><TD>" & .Cells(8, 8).Value & "</TD></TR>"
            myHTML = myHTML & "<TR><TD>" & "Total YY" & "</TD><TD>" & .Cells(9, 8).Value & "</TD></TR>"
            myHTML = myHTML & Chr(10) & "</TABLE><br>"
            myHTML = myHTML & Chr(10) & "<FONT SIZE=2></FONT></BODY>"
            myHTML = myHTML & Chr(10) & "</HTML>"
            myHTML = Replace(myHTML, Chr(10), "</p><p>")
             myHTML = myHTML & Chr(10) & "</HTML>"
            myHTML = Replace(myHTML, Chr(10), "</p><p>")
            On Error GoTo ErrHandler
            FileNum = FreeFile
            Open myFileName For Output As #FileNum
            Print #FileNum, myHTML
            Close #FileNum
        End With
        MsgBox "File " & myFileName & " was created."
        
        retVal = Shell("C:\Program Files\Internet Explorer" _
        & "\IEXPLORE.EXE " & myFileName, vbNormalFocus)
        
        Exit Sub
        
    ErrHandler:
        MsgBox "Some sort of error occurred while creating file."
    End Sub



    Monday, July 6, 2015 3:24 PM
  • Hi Bernie,

    Hope you enjoyed a lot. ;)

    I've tested your code but it seems both the tables are appearing one after another. However, I want them to be appeared side by side. 

    Thanks.

    Monday, July 6, 2015 5:51 PM
  • Sorry, missed that part:

    Sub TwoTablesSideBySide()
        Dim myFileName As String
        Dim myHTML As String
        Dim FileNum As Integer
        Dim myC As Integer
        Dim myR As Long
        Dim retVal As Variant
         Dim b As Long
        

        myFileName = ThisWorkbook.Path & "\Test.html"
        
        With Worksheets("Sheet2")
            b = .Cells(.Rows.Count, "A").End(xlUp).Row - 2
            .Range("H8").Formula = "=COUNTIF(C:C,""XX"")"
            .Range("H9").Formula = "=COUNTIF(C:C,""YY"")"
           
            myHTML = "<HTML>"
            myHTML = myHTML & Chr(10) & "<HEAD>"
            myHTML = myHTML & Chr(10) & "<META HTTP-EQUIV=""Content-Type"" CONTENT=""text/html; charset=windows-1252"">"
            myHTML = myHTML & Chr(10) & "<META NAME=""Generator"" CONTENT=""Microsoft Word 97"">"
            myHTML = myHTML & Chr(10) & "</HEAD>"
            myHTML = myHTML & Chr(10) & "<TABLE BORDER=""5"" style=""float: left"">"
            myHTML = myHTML & Chr(10) & "<TR>"
            myHTML = myHTML & Chr(10) & "<TH COLSPAN=""4"">"
            myHTML = myHTML & Chr(10) & "<H3><BR>Test Log</H3></TH>"
            myHTML = myHTML & Chr(10) & "</TR>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 1).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 2).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 3).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 4).Value & "</TH>"
            
            For myR = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
                
                myHTML = myHTML & "<TR><TD>" & .Cells(myR, 1).Value & "</TD>" & _
                                        "<TD>" & .Cells(myR, 2).Value & "</TD>" & _
                                        "<TD>" & .Cells(myR, 3).Value & "</TD>" & _
                                        "<TD>" & .Cells(myR, 4).Value & "</TD></TR>"
            Next myR
            myHTML = myHTML & Chr(10) & "</TABLE>"
            myHTML = myHTML & Chr(10) & "<TABLE BORDER=""5"" style=""float: left"">"
            myHTML = myHTML & Chr(10) & "<TH COLSPAN=""2""><H3><BR>Status</H3></TH>"
            myHTML = myHTML & "<TR><TD>" & "Total Point" & "</TD><TD>" & b & "</TD></TR>"
            myHTML = myHTML & "<TR><TD>" & "Total XX" & "</TD><TD>" & .Cells(8, 8).Value & "</TD></TR>"
            myHTML = myHTML & "<TR><TD>" & "Total YY" & "</TD><TD>" & .Cells(9, 8).Value & "</TD></TR>"
            myHTML = myHTML & Chr(10) & "</TABLE><br>"
            myHTML = myHTML & Chr(10) & "<FONT SIZE=2></FONT></BODY>"
            myHTML = myHTML & Chr(10) & "</HTML>"
            myHTML = Replace(myHTML, Chr(10), "</p><p>")
             myHTML = myHTML & Chr(10) & "</HTML>"
            myHTML = Replace(myHTML, Chr(10), "</p><p>")
            On Error GoTo ErrHandler
            FileNum = FreeFile
            Open myFileName For Output As #FileNum
            Print #FileNum, myHTML
            Close #FileNum
        End With
        MsgBox "File " & myFileName & " was created."
        
        retVal = Shell("C:\Program Files\Internet Explorer" _
        & "\IEXPLORE.EXE " & myFileName, vbNormalFocus)
        
        Exit Sub
        
    ErrHandler:
        MsgBox "Some sort of error occurred while creating file."
    End Sub

    • Marked as answer by Ed_Dao Tuesday, July 7, 2015 6:01 AM
    Monday, July 6, 2015 7:49 PM
  • Hi Bernie,

    Thanks for your help. Both the tables are appearing next to each other. But there is no space between those tables. It would be better if I have some space between them. Anyways, I'm marking it as answer.

    Thanks.

    Tuesday, July 7, 2015 6:01 AM
  • Here's some space between the two:

    Sub TwoTablesSideBySide()
        Dim myFileName As String
        Dim myHTML As String
        Dim FileNum As Integer
        Dim myC As Integer
        Dim myR As Long
        Dim retVal As Variant
         Dim b As Long
        

        myFileName = ThisWorkbook.Path & "\Test.html"
        
        With Worksheets("Sheet2")
            b = .Cells(.Rows.Count, "A").End(xlUp).Row - 2
            .Range("H8").Formula = "=COUNTIF(C:C,""XX"")"
            .Range("H9").Formula = "=COUNTIF(C:C,""YY"")"
           
            myHTML = "<HTML>"
            myHTML = myHTML & Chr(10) & "<HEAD>"
            myHTML = myHTML & Chr(10) & "<META HTTP-EQUIV=""Content-Type"" CONTENT=""text/html; charset=windows-1252"">"
            myHTML = myHTML & Chr(10) & "<META NAME=""Generator"" CONTENT=""Microsoft Word 97"">"
            myHTML = myHTML & Chr(10) & "</HEAD>"
            myHTML = myHTML & Chr(10) & "<TABLE style=""MARGIN: 0px 100px"" BORDER=""5"" style=""float: left"">"
            myHTML = myHTML & Chr(10) & "<TR>"
            myHTML = myHTML & Chr(10) & "<TH COLSPAN=""4"">"
            myHTML = myHTML & Chr(10) & "<H3><BR>Test Log</H3></TH>"
            myHTML = myHTML & Chr(10) & "</TR>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 1).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 2).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 3).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 4).Value & "</TH>"
            
            For myR = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
                
                myHTML = myHTML & "<TR><TD>" & .Cells(myR, 1).Value & "</TD>" & _
                                        "<TD>" & .Cells(myR, 2).Value & "</TD>" & _
                                        "<TD>" & .Cells(myR, 3).Value & "</TD>" & _
                                        "<TD>" & .Cells(myR, 4).Value & "</TD></TR>"
            Next myR
            myHTML = myHTML & Chr(10) & "</TABLE>"
            myHTML = myHTML & Chr(10) & "<TABLE BORDER=""5"" HSPACE=""50"" style=""float: left"">"   ' style=""float: left""
            myHTML = myHTML & Chr(10) & "<TH COLSPAN=""2""><H3><BR>Status</H3></TH>"
            myHTML = myHTML & "<TR><TD>" & "Total Point" & "</TD><TD>" & b & "</TD></TR>"
            myHTML = myHTML & "<TR><TD>" & "Total XX" & "</TD><TD>" & .Cells(8, 8).Value & "</TD></TR>"
            myHTML = myHTML & "<TR><TD>" & "Total YY" & "</TD><TD>" & .Cells(9, 8).Value & "</TD></TR>"
            myHTML = myHTML & Chr(10) & "</TABLE><br>"
            myHTML = myHTML & Chr(10) & "<FONT SIZE=2></FONT></BODY>"
            myHTML = myHTML & Chr(10) & "</HTML>"
            myHTML = Replace(myHTML, Chr(10), "</p><p>")
             myHTML = myHTML & Chr(10) & "</HTML>"
            myHTML = Replace(myHTML, Chr(10), "</p><p>")
            On Error GoTo ErrHandler
            FileNum = FreeFile
            Open myFileName For Output As #FileNum
            Print #FileNum, myHTML
            Close #FileNum
        End With
        MsgBox "File " & myFileName & " was created."
        
        retVal = Shell("C:\Program Files\Internet Explorer" _
        & "\IEXPLORE.EXE " & myFileName, vbNormalFocus)
        
        Exit Sub
        
    ErrHandler:
        MsgBox "Some sort of error occurred while creating file."
    End Sub

    • Marked as answer by Ed_Dao Tuesday, July 7, 2015 5:17 PM
    Tuesday, July 7, 2015 1:37 PM