You probably know that you can save an Excel workbook in HTML format. I do it for my golf league and it works fine. It also generates a pig of an HTML file, mainly because Microsoft is trying to have high fidelity (make it look the same in the browser as it does in the desktop).
For my most recent sports-related project, I just didn’t want such a large file. I run an NFL survivor pool. You can read all the rules if you like, but basically each participant selects one NFL team per week. If that team wins, the participant survives. If they lose, they’re done. Whoever is left at the end is the winner. I needed a quick and easy way to update the results on a web page.
I start with this spreadsheet:
Bold teams means a loss. Italicized teams are winners. Unformatted teams means they haven’t played yet (or I haven’t updated yet). The end result is this:
I found a bunch of images of NFL helmets and a free green checkmark image on the interwebs. Now all I have to do is read the data and convert it to HTML. Here’s the code:
Sub MakeHmtl()
Dim rRow As Range
Dim rCell As Range
Dim sHtml As String
Dim sBody As String
Dim sTable As String
Dim sRow As String
Dim bLoss As Boolean
Dim lFnum As Long
Dim sFname As String
Const sPAIDIMG As String = "<img src=""checkmark.png"" />"
'header
sHtml = Tag("DK Survivor Pool", "title") & vbNewLine
sHtml = sHtml & "<link rel=""stylesheet"" href=""style.css"">"
sHtml = Tag(sHtml, "head", , True) & vbNewLine
'body
sBody = Tag("DK Survivor Pool", "h1") & vbNewLine
sBody = sBody & Tag("Updated: " & Format(Now, "yyyy-mmm-dd hh:mm AM/PM"), "p") & vbNewLine
sBody = sBody & Tag(Tag("Rules", "a", "href = ""survivorrules.html"""), "p") & vbNewLine
'table
For Each rRow In Sheet1.Range("A2:S13").Rows
bLoss = False
For Each rCell In rRow.Cells
If rCell.Column = 1 Or rCell.Row = 2 Then
sRow = sRow & AddClass(Tag(rCell.Value, "td"), "name")
ElseIf rCell.Column = 2 Then
If IsEmpty(rCell.Value) Then
sRow = sRow & Tag("", "td")
Else
sRow = sRow & Tag(sPAIDIMG, "td")
End If
Else
Select Case True
Case rCell.Font.Bold
sRow = sRow & AddClass(Tag(MakeImage(rCell.Value), "td"), "loss")
bLoss = True
Case rCell.Font.Italic
sRow = sRow & AddClass(Tag(MakeImage(rCell.Value), "td"), "win")
Case IsEmpty(rCell.Value)
If bLoss Then
sRow = sRow & AddClass(Tag("", "td"), "loss")
Else
sRow = sRow & Tag("", "td")
End If
Case Else
sRow = sRow & Tag(MakeImage(rCell.Value), "td")
End Select
End If
Next rCell
sTable = sTable & Tag(sRow, "tr") & vbNewLine
sRow = """"
Next rRow
sBody = sBody & Tag(sTable, "table", "border=""1"" cellpadding=""5""", True)
sHtml = sHtml & Tag(sBody, "body", , True)
sHtml = Tag(sHtml, "html", , True)
If Len(Dir("C:Test_Datadebug.ini")) = 0 Then
sFname = "C: UsersdickDropboxSportsSurvivorindex.html"""
Else
sFname = "C:UsersdickMy DocumentsMy DropboxSportsSurvivorindex.html"""
End If
lFnum = FreeFile
Open sFname For Output As lFnum
Print #lFnum, sHtml
Close lFnum
End Sub
Creating text files from scratch can be a pain in the butt, but HTML files are worse. You have to get all those tags right and properly closed. I hate unindented HTML, so there’s more work there too. Generally I try to work from the inside out on HTML files. That way I can a pass a couple of arguments into a function to make the tags and be assured that I don’t miss something. Take the header section for example. First I pass “DK Survivor Pool” and “title” into the Tag function. That function looks like this:
Function Tag(sValue As String, sTag As String, Optional sAttr As String = "", Optional bIndent As Boolean = False) As String
Dim sReturn As String
If Len(sAttr) > 0 Then
sAttr = Space(1) & sAttr
End If
If bIndent Then
sValue = vbTab & Replace(sValue, vbNewLine, vbNewLine & vbTab)
sReturn = "< " & sTag & sAttr & ">" & vbNewLine & sValue & vbNewLine & "< /" & sTag & ">"
Else
sReturn = "< " & sTag & sAttr & ">" & sValue & "< /" & sTag & ">"
End If
Tag = sReturn
End Function
(WordPress doesn’t like HTML so there’s an extra space in the closing tag part.) I get back something that looks like this
<title>DK Survivor Pool</title>
Next, I append the link tag manually because it doesn’t really fit into my inside-out dynamic. Finally, I call sHtml = Tag(sHtml, "head", , True) & vbNewLine to wrap what I have in a head tag. I also set the optional Indent argument to true and get this:
<head>
<title>DK Survivor Pool</title>
<link rel="stylesheet" href="style.css" />
</head>
The code wraps whatever I send it, in whatever tag I send it, and indents along the way. The other optional argument is for including attributes within the tag. I want my table tag to have border=”1″ and cellpadding=”5″ so I supply those to the function when needed. I use the class attribute a lot to format winners and losers. I created a separate function to add a class attribute so I wouldn’t have to type it in the code.
Function AddClass(sTag As String, sClass As String) As String
AddClass = Replace(sTag, ">", " class=""" & sClass & """>", 1, 1)
End Function
The last helper function is to create an image tag. My Tag function is good for enclosing something in opening and closing tags. The image tag is self-closing, so it gets its own home. On the spreadsheet, I record the team name so that it matches the image file name. If I type “eagles” for a Philadelphia pick, the MakeImage function returns <img src="eaglesleft.bmp" />
Function MakeImage(sValue As String) As String
MakeImage = "<img src=""" & sValue & "left.bmp"" />"
End Function
The main code basically loops through all the cells, determines the HTML necessary, and appends it to one long string. That string is then written to a file. Once complete, I manually FTP that file up to my web server. A couple of other notes on the code:
Losing teams are colored red and that participant doesn’t get to select any more. I wanted all the succeeding weeks to be red also. I use the bLoss variable to handle this. When I get to a loss, I set bLoss to True. When an empty cell is detected (no selection yet), I add the “loss” class to the td tag to color it red.
The last part is the location of the file. My dropbox folder is in two different places on two different computers. I’m not sure why this is, but I think it relates to which operating system was installed when I installed Dropbox. Back in the Windows XP days, Dropbox put it in My DocumentsMy Dropbox and in Windows 7, it’s directly under the user folder and they dropped the “My”. At least I think that’s what happened. To differentiate the two, I found a file that I’m absolutely sure is on one computer and absolutely sure isn’t on the other. I use Len(Dir(..)) to test the existence of the file and change the path accordingly. I think we both know this will break some time in the future, but it works for now.
One of the downsides to the inside-out approach for concatenating HTML is code readability. If I’m just building a string one character at a time, it’s pretty easy to follow along. When I use functions to wrap strings in tags, it’s a little harder. You might expect that the html opening tag would be near the top of the procedure, but it’s actually the last tag I add because it’s “outside”.
Finally, if you’re new to creating big strings in VBA, you should note that to embed a double quote into a string, you use two double quotes in succession.
