www.ebbemunk.dkarrowEbbe's HTML Generator Detail Page

Ebbe's HTML Generator Detail Page

Figure 1

Download (160 kB):  

ebbes_html_generator.xls

Here is a useful Excel spreadsheet with two macros. I use them for maintaining www.guldsider.dk, a website with Danish Gold and Silver shops on the Internet. Bruno Munkholm wrote the Visual Basic, and the file is free for all.

This Excel file can maintain a lot of content files and make them much easier to keep updated. You maintain your information in an Excel workbook and use the macros to produce updated HTML files. The system can be set up with automatically created links between the HTML files, links to style sheets, and links to images.

The generated HTML files have some advantages compared to a Content Management System: The server is delivering the ready-made files quicker than a CMS server,

you may use any kind of web server, and you decide the HTML design by yourself.

This Excel workbook is easy to use, and it is for free, but... to use it you need a certain level of knowledge of Excel and HTML. Take care, because there is no check on the internal or external consistency. Please note the Proposals for Testing below.

There are three worksheets in the workbook:

  1. database worksheet to enter and edit data

  2. content_file worksheet to create one or all HTML content files

  3. table_row worksheet to create a formatted HTML table for an index file, etc.

Description of the Three Worksheets

1. The database Worksheet

The database worksheet provides the other worksheets with data. The worksheet contains horizontal data records. The two top rows are meant for headlines – they are not read by the macro.

Database Key in Column C

In column C is a key consisting of letters and numbers. This key will be used for the VLOOKUP Excel command and for the HTML file names. Three rules:

  1. The first key value must be in field C3

  2. The last key must be followed by a blank

  3. The keys must be unique, but need not to be sorted ascendingly

The yellow area shows the key that will be read by the macro.

Further Details

Figure 2

2. The content_file Worksheet and Macro

This worksheet is a mix-up between HTML statements and VLOOKUP formulas. The two top rows are meant for set-up of the VLOOKUP formulas and are not converted to HTML like the rest of the page. One rule:

Most of the description in this section applies to the table_row worksheet as well.

VLOOKUP Example

In field A2 is entered the value 10, which actually is the first key in the database worksheet

In field E10 is entered the formula =VLOOKUP($A$2;database!$C$3:$H$6;1;FALSE)

Resulting formula value: "10".

Note the response if you change the value in field A2 to the next key "12a".

Explanation of the VLOOKUP Formula

VLOOKUP is a short form for "vertical lookup". The command consists of four items separated by semicolons (or commas, depending on your country code):

  1. lookup_value: Which key is the one to search for? In this context it is always the field $A$2 in the second row of the sheet

  2. table_array: Which array contains the data to be shown? In this context it is always the complete database on the database worksheet database!$C$3:$H$6. Take care:

  1. col_index_num: Which information from the row do you want to place here? In this context it is the key value itself. The col_index_num is the only part of the formula that you need to change when you want other information from the database row.

  2. range_lookup: This is a logical value. In this context the value must always be "FALSE" to specify that Excel always must find an exact match.

Empty Cells and Cells with an Asterisk

Note, that a row will not be empty, if it contains a combination of HTML code and an empty cell from the database. To avoid this situation there is a special feature: If you place an asterisk in a database field, then the macro will omit all of the Excel row's HTML and VLOOKUP content.

Empty cells can be difficult to manage in an HTML table. It may be converted to a non-breaking space (&nbsp;). The appearance is rather unpredictible in various browsers. I propose you to enter a full stop (.) in the empty cell. That will normally be converted to a full stop enclosed in paragraph signs (<p>.</p>), which is easier to manage.

The content_file Macro

Single file: The content_file macro produces a single HTML file in the same directory as the Excel file. The HTML file name will be as the key name in field A2. In the present example, it will be "10.html". If there is already an HTML file with this name it will be overwritten by the new file.

The macro is started in Tools/Macro/Macros. It will continue till it reaches the </html> tag in column A.

All files: If you type "ALL" in field D2, the macro will create an HTML file or every row in the database worksheet.

Further Details

Comments

In this example I use the boys' names as key for the images. Please note, that this demands that the boys' names are unique.

3. The table_row Worksheet and Macro

The table_row macro can be used to create a lot of rows for an HTML table, for example to be inserted into an index file. It is using a combination of HTML and VLOOKUP formulas like in the Content Pages worksheet. Rules:

  1. You should only enter the <td> values for one table row, and the macro will repeat these for every key in the database

  2. All <td> tags must begin in column B

  3. The last <td> tag must be followed by an empty line

The table_row Macro

The table_row macro creates a complete HTML file with one table and as many rows as there are rows in the database worksheet. The macro adds a suitable amount of <table> and <tr> tags automatically. The result is saved in a file called table_row.html in the same directory as the Excel file. If there is already an HTML file with this name it will be overwritten.

The macro is started in Tools/Macro/Macros. It will run till it finds an empty cell in column B, and will then repeat with the following database key, etc.

Further Details

Proposals for Testing

Internal test, including blank values.

  1. The internal transfer of data from the database worksheet to the other worksheets is heavily dependent of your use of the VLOOKUP formula; check the formulas' results

  2. On the content_file and table_row worksheets you should check all formula results by entering the first and last key value in field A1.

External test, including blank values. Check the result in at least two browsers:

  1. Create single HTML pages for these two key values and check that all information is present as expected in the pages – including blank values

  2. On the table_row worksheet: Create the HTML page and check that all values are transferred as expected – including empty cells.

Disclaimer

Nielsen's First Law of Computer Manuals

People don't read documentation voluntarily! (See www.useit.com)

HTML Results of the Example

Download images (25 kB):  

generator_images.zip

Unzip the files to a folder named images below the HTML content folder.

If you run the macros using the default content, I hope you will get a result like the files in the frames below. Please try the links to see the automatically created connections: