What a Web page is

A program is a bunch of statements in a language. Web pages are similar. They're a bunch of tags in a language called HTML (for hypertext markup language). HTML isn't a programming language, though. It's a markup language.

Some sample HTML:

<h1>Welcome</h1>
<p>Welcome to the official Web site of the <strong>Doom Monkeys</strong>. Heavy metal, with bananas, and despair.</p>

<h1>, <p>, and <strong> are tags. They tell a Web browser how to show text. <h1> says to make a big heading. <p> says to make a paragraph. <strong> says to make strong text, usually bold.

Note that for each opening tag (e.g., <p>), there's a closing tag (e.g., </p>).

Tags nest. For example, <strong> and </strong> are inside <p> and </p>. This is OK:

<p>I am not a <strong>cow</strong>.</p>

This is not OK:

<p>I am not a <strong>cow</p>.</strong>

Inner tags must close before outer tags.

Give the tags to a browser, and get something like:

You can see the heading, the paragraph, and the strong text.

Making a Web page in VBA is easy.

Open a file
Write out some HTML tags
Close the file.

Tip program on the Web

The tip program we looked at long ago looks like this:

Here's the code:

1. Dim amount as Single
2. Dim tip as Single
3. Dim total as Single
4. amount = Cells(1,2)
5. tip = amount * 0.15
6. total = amount + tip
7. Cells(2, 2) = tip
8. Cells(3, 2) = total

Let's change it, so it creates a Web page as well. Here's the pattern for the part of the program that makes a page:

Open a file
Write out some HTML tags
Close the file.

Opening and closing the file

Let's put the tip output in the file tips.html. Web pages should end with the .html extension, so your computer knows to show them in a browser when you double click on them.

This code will work:

Open ThisWorkbook.Path & "\tips.html" For Output As #1
'Write out HTML code.
Close #1

ThisWorkbook.Path tells VBA what folder to put the file in: the same one as the workbook. So if your workbook is C:\User\phlobert\Documents\tip-web.xlsm, then the output will go to C:\User\phlobert\Documents\tips.html.

What's that #1 about? That's the file handle. When you want to VBA to write to a file, you use the number you gave it in the Open statement. It doesn't have to be 1. For example, this would work:

Open ThisWorkbook.Path & "\tips.html" For Output As #7
'Write out HTML code.
Close #7

Output HTML

Remember that <p> is the HTML paragraph tag. So if we put…

<p>Wait, what?</p>

… into an HTML file, a browser would show it as a paragraph.

The Print statement outputs data to a file. So we could have:

Open ThisWorkbook.Path & "\tips.html" For Output As #1
Print #1, "<p>Wait, what?</p>"
Close #1

Open the HTML file in a browser, and see:

Let's add some code to the end of the tip program:

9. Open ThisWorkbook.Path & "\tips.html" For Output As #1
10. Print #1, "<p>Amount: " & amount & "</p>"
11. Print #1, "<p>Tip: " & tip & "</p>"
12. Print #1, "<p>Total: " & total & "</p>"
13. Close #1

You can see the pattern at work:

Open a file
Write out some HTML tags
Close the file.

When I tried it with…

… the file tips.html had this in it:

<p>Amount: 15</p>
<p>Tip: 2.25</p>
<p>Total: 17.25</p>

I opened tips.html is a browser, by double-clicking on it. This is what I got:

Appending

Here's that Open again.

Open ThisWorkbook.Path & "\tips.html" For >>Output<< As #1

Output tells Excel to erase tips.html if it already exists, and make a new file. But let's say we wanted to keep every tip calculation. When we did a new one, we want to add it to the end of the file, leaving the existing ones intact.

Here's how.

9. Open ThisWorkbook.Path & "\tips.html" For Append As #1
10. Print #1, "<p>Amount: " & amount & "</p>"
11. Print #1, "<p>Tip: " & tip & "</p>"
12. Print #1, "<p>Total: " & total & "</p>"
13. Print #1, "<hr>"
14. Close #1

Change Output to Append (line 9). Now Excel will add to the end of the file, rather than overwriting it each time the program runs.

The line…

Print #1, "<hr>"

… adds a new HTML tag after each tip calculation. hr stands for "horizontal rule," meaning a horizontal line. By adding this tag, we separate each calculation with a line.

Here's what was in tips.html after I ran the program three times:

1. <p>Amount: 15</p>
2. <p>Tip: 2.25</p>
3. <p>Total: 17.25</p>
4. <hr>
5. <p>Amount: 22</p>
6. <p>Tip: 3.3</p>
7. <p>Total: 25.3</p>
8. <hr>
9. <p>Amount: 18</p>
10. <p>Tip: 2.7</p>
11. <p>Total: 20.7</p>
12. <hr>

I started by erasing the old tips.html. Then I typed 15 into the cell, and clicked the Run button. The program wrote lines 1 to 4 to the file.

Then I typed 22 in the cell, and clicked Run again. The program added lines 5 to 8 to the end of tips.html.

I typed 18 in the cell, and clicked Run again. The program added lines 9 to 12 to the end of the file.

I double-clicked on tips.html. The browser opened, and showed this:

Get the file name from a cell

We've been sending all of the HTML to tips.html. Now, suppose we did this in the worksheet:

Cell B5 has the name of the file we want to write to. Here's some changes to the code:

Dim fileName As String
...
fileName = Cells(5, 2)
Open ThisWorkbook.Path & "\" & fileName For Append As #1

Here's what we had before:

Open ThisWorkbook.Path & "\tips.html" For Append As #1

The only difference is that the file name comes from a cell.

Summary

A Web page is a bunch of HTML tags, like <p>Wait, what?</p>. VBA programs can make Web pages, by writing HTML tags to a file. For example:

Open ThisWorkbook.Path & "\tips.html" For Output As #1
Print #1, "<p>Wait, what?</p>"
Close #1

If you open the file in append mode, new output is added to the end of the file.