CODEWITHSUNDEEP
htmlvbaexcel
Jono
Jono

Reputation: 3

Create HTML Tables with Excel VBA

Total newbie to VBA - trying to automatically create a HTML table with Data from an Excel spreadsheet.

I've got data in Columns I-M from rows 2 down. This could be anywhere from 1 row to 500 rows.

I then also have another piece of data in P17-26 which is static but obviously values change.

I had a look at answers in this thread: Dynamically create HTML table rows with VBA

Sub CreateAlternatorHtml()

Dim wb As Workbook
Dim ws As Worksheet
Dim coll As New Collection
Dim lngCounter As Long
Dim strBeforeRows As String
Dim strApplication As String
Dim strBetweenRows As String
Dim strSpecs As String
Dim strAfterRows As String
Dim strAll As String

' get a worksheet reference
Set wb = ActiveWorkbook
Set ws = Sheets("Copy From Here")


' test collection
coll.Add 3


' HTML before rows
   strBeforeRows = "<style type=""text/css""> table.tableizer-table { border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif font-size: 12px;} .tableizer-table td { padding: 4px; margin: 3px; border: 1px solid #ccc;}.tableizer-table th { background-color: #104E8B;  color: #FFF; font-weight: bold;}</style>" & _
    "<table width=""100%"" class=""tableizer-table"">" & _
    "<tr class=""tablezer-firstrow"">" & _
    "<th><p>Make</p></th>" & _
    "<th>Model</th>" & _
    "<th>Series</th>" & _
    "<th>Engine</th>" & _
    "<th>Years (mm/yy)</th></tr>"


' iterate collection
strApplication = ""
For lngCounter = 1 To coll.Count
    strApplication = strApplication & "<tr>"
    strApplication = strApplication & "<td><div align=""center"">" & ws.Range("I" & coll(lngCounter)).Value & "</div></td>"
    strApplication = strApplication & "<td><div align=""center"">" & ws.Range("J" & coll(lngCounter)).Value & "</div></td>"
    strApplication = strApplication & "<td><div align=""center"">" & ws.Range("K" & coll(lngCounter)).Value & "</div></td>"
    strApplication = strApplication & "<td><div align=""center"">" & ws.Range("L" & coll(lngCounter)).Value & "</div></td>"
    strApplication = strApplication & "<td><div align=""center"">" & ws.Range("M" & coll(lngCounter)).Value & "</div></td>"
    strApplication = strApplication & "</tr>"
Next lngCounter

' HTML between rows
strBetweenRows = "<tr>" & _
    "<td>&nbsp;</td>" & _
    "<td>&nbsp;</td>" & _
    "<td>&nbsp;</td>" & _
    "<td>&nbsp;</td>" & _
    "<td>&nbsp;</td>" & _
    "</tr>" & _
    "<tr>" & _
    "<th colspan=""2"" class=""tableizer-firstrow"">Specifications</th>" & _
    "<td>&nbsp;</td>" & _
    "<td>&nbsp;</td>" & _
    "<td>&nbsp;</td></tr> <tr>"


' Specs HTML
strSpecs = ""
    strSpecs = strSpecs & "<tr><td>Volts</td><td align=""right"" width=""120""><div align=""center"">" & ws.Range("P17").Value & "</div></td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr>"
    strSpecs = strSpecs & "<tr><td>Amps</td><td align=""right"" width=""120""><div align=""center"">" & ws.Range("P18").Value & "</div></td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr>"
    strSpecs = strSpecs & "<tr><td>Adjustment Hole (mm)</td><td align=""right"" width=""120""><div align=""center"">" & ws.Range("P19").Value & "</div></td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr>"
    strSpecs = strSpecs & "<tr><td>Pivot Hole (mm)<</td><td align=""right"" width=""120""><div align=""center"">" & ws.Range("P20").Value & "</div></td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr>"
    strSpecs = strSpecs & "<tr><td>Adjustment to Pivot (mm)</td><td align=""right"" width=""120""><div align=""center"">" & ws.Range("P20").Value & "</div></td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr>"
    strSpecs = strSpecs & "<tr><td>Pivot Length (mm)</td><td align=""right"" width=""120""><div align=""center"">" & ws.Range("P22").Value & "</div></td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr>"
    strSpecs = strSpecs & "<tr><td>Inside Feet To Feet (mm)</td><td align=""right"" width=""120""><div align=""center"">" & ws.Range("P23").Value & "</div></td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr>"
    strSpecs = strSpecs & "<tr><td>Pulley (mm)</td><td align=""right"" width=""120""><div align=""center"">" & ws.Range("P24").Value & "</div></td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr>"
    strSpecs = strSpecs & "<tr><td>No Of Grooves</td><td align=""right"" width=""120""><div align=""center"">" & ws.Range("P25").Value & "</div></td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr>"
    strSpecs = strSpecs & "<tr><td>Plug</td><td align=""right"" width=""120""><div align=""center"">" & ws.Range("P26").Value & "</div></td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr>"


' HTML after rows
strAfterRows = "</tr></table>"

' final HTML - concatenate the 3 string variables
strAll = strBeforeRows & strApplication & strBetweenRows & strSpecs & strAfterRows  
Debug.Print strAll 
End Sub

This is what I've got so far, and it produces this HTML, but How do I get it to loop thru the rows that are in I-M

<style type="text/css">
    table.tableizer-table { border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif font-size: 12px;}
    .tableizer-table td { padding: 4px; margin: 3px; border: 1px solid #ccc;}
    .tableizer-table th { background-color: #104E8B;  color: #FFF; font-weight: bold;}
</style>
<table width="100%" class="tableizer-table">
<tr class="tablezer-firstrow">
    <th>
        <p>Make</p>
    </th>
    <th>Model</th>
    <th>Series</th>
    <th>Engine</th>
    <th>Years (mm/yy)</th>
</tr>
<tr>
    <td>
        <div align="center">Ford</div>
    </td>
    <td>
        <div align="center">Focus</div>
    </td>
    <td>
        <div align="center">LR</div>
    </td>
    <td>
        <div align="center">2.0L 4cyl Petrol - EDD#</div>
    </td>
    <td>
        <div align="center">06/02 - 04/05</div>
    </td>
</tr>
<tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
</tr>
<tr>
    <th colspan="2" class="tableizer-firstrow">Specifications</th>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
</tr>
<tr>
<tr>
    <td>Volts</td>
    <td align="right" width="120">
        <div align="center">12</div>
    </td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
</tr>
<tr>
<tr>
    <td>Amps</td>
    <td align="right" width="120">
        <div align="center">90</div>
    </td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
</tr>
<tr>
<tr>
    <td>Adjustment Hole (mm)</td>
    <td align="right" width="120">
        <div align="center">8</div>
    </td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
</tr>
<tr>
<tr>
    <td>Pivot Hole (mm)<</td>
    <td align="right" width="120">
        <div align="center">8</div>
    </td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
</tr>
<tr>
<tr>
    <td>Adjustment to Pivot (mm)</td>
    <td align="right" width="120">
        <div align="center">8</div>
    </td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
</tr>
<tr>
<tr>
    <td>Pivot Length (mm)</td>
    <td align="right" width="120">
        <div align="center"></div>
    </td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
</tr>
<tr>
<tr>
    <td>Inside Feet To Feet (mm)</td>
    <td align="right" width="120">
        <div align="center">64</div>
    </td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
</tr>
<tr>
<tr>
    <td>Pulley (mm)</td>
    <td align="right" width="120">
        <div align="center">53</div>
    </td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
</tr>
<tr>
<tr>
    <td>No Of Grooves</td>
    <td align="right" width="120">
        <div align="center">6PV</div>
    </td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
</tr>
<tr>
<tr>
    <td>Plug</td>
    <td align="right" width="120">
        <div align="center">RP222</div>
    </td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
</tr>
<tr></tr>
</table>

Upvotes: 0

Views: 5631

Answers (1)

NickSlash
NickSlash

Reputation: 5100

Assuming your issue is with the following section of code:

' iterate collection
strApplication = ""
For lngCounter = 1 To coll.Count
    strApplication = strApplication & "<tr>"
    strApplication = strApplication & "<td><div align=""center"">" & ws.Range("I" & coll(lngCounter)).Value & "</div></td>"
    strApplication = strApplication & "<td><div align=""center"">" & ws.Range("J" & coll(lngCounter)).Value & "</div></td>"
    strApplication = strApplication & "<td><div align=""center"">" & ws.Range("K" & coll(lngCounter)).Value & "</div></td>"
    strApplication = strApplication & "<td><div align=""center"">" & ws.Range("L" & coll(lngCounter)).Value & "</div></td>"
    strApplication = strApplication & "<td><div align=""center"">" & ws.Range("M" & coll(lngCounter)).Value & "</div></td>"
    strApplication = strApplication & "</tr>"
Next lngCounter

I don't really see why you've used a collection since it seems to just be an index/counter for your loop. Try the following:

Dim MinRow As Intger
Dim MaxRow As Integer
Dim Counter As Integer
MinRow = 2 ' from your question, this is the first row of data?
MaxRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "I").End(xlUp).Row ' Find the last populated row

For Counter = MinRow to MaxRow
    strApplication = strApplication & "<tr>"
    strApplication = strApplication & "<td><div align=""center"">" & ws.Range("I" & Counter).Value & "</div></td>"
    strApplication = strApplication & "<td><div align=""center"">" & ws.Range("J" & Counter).Value & "</div></td>"
    strApplication = strApplication & "<td><div align=""center"">" & ws.Range("K" & Counter).Value & "</div></td>"
    strApplication = strApplication & "<td><div align=""center"">" & ws.Range("L" & Counter).Value & "</div></td>"
    strApplication = strApplication & "<td><div align=""center"">" & ws.Range("M" & Counter).Value & "</div></td>"
    strApplication = strApplication & "</tr>"
Next Counter

Upvotes: 1

Related Questions