Downloads HTML Generator|
www.ebbemunk.dk
Downloads HTML Generator |
|
Download (160 kB): |
|
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:
database worksheet to enter and edit data
content_file worksheet to create one or all HTML content files
table_row worksheet to create a formatted HTML table for an index file, etc.
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.
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:
The first key value must be in field C3
The last key must be followed by a blank
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.
The key values may be any combination of letters and numbers. The key will be used for HTML file names, so take care for uppercase letters, as UNIX-based servers distinguish between uppercase and lowercase letters in file names.
There is no limit for the number of lines or columns except the computer's memory and Excel's limit of 256 columns and 65536 rows
Cells only containing an asterisk (*) are treated in a special way by the macros. Empty cells and cells only containing spaces will be treated like all other cells.
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:
The content must end with a cell in column A containing the HTML tag </html>
Most of the description in this section applies to the table_row worksheet as well.
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".
VLOOKUP is a short form for "vertical lookup". The command consists of four items separated by semicolons (or commas, depending on your country code):
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
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:
if you change the size of the database, then all VLOOKUP formulas must be corrected
always use the dollar signs (F4) in this formula, or Excel will change the parameters when copied to another position!
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.
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.
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 ( ). 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.
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.
To make the macro work you may need to change your macro security level to Medium (Tools/Options/Security/Macro Security/Medium) and accept to open with macros every time you open the workbook.
Keep the layout of the VLOOKUP formulas bold to be able to recognise them from the HTML code
It may be difficult to paste HTML code into the worksheet, as Excel tries to show the code as it is meant to look in the browser (WYSIWYG). If you encounter this problem, you can solve it by copying and pasting each statement separately. If you still have problems, then press F2 before pasting.
All empty cells will disappear in the resulting HTML code except leading, empty cells which will be converted into leading tabs
There is no limit for the number of lines or columns except the computer's memory and Excel's limit of 256 columns and 65536 rows.
In this example I use the boys' names as key for the images. Please note, that this demands that the boys' names are unique.
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:
You should only enter the <td> values for one table row, and the macro will repeat these for every key in the database
All <td> tags must begin in column B
The last <td> tag must be followed by an empty line
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.
If you want to edit the <table> or <tr> tags you must edit it in the table_row.html file.
The rules for asterisks appear here as well: If you place an asterisk in a database field, then the macro will omit all of the Excel row's HTML and VLOOKUP content.
Internal test, including blank values.
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
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:
Create single HTML pages for these two key values and check that all information is present as expected in the pages – including blank values
On the table_row worksheet: Create the HTML page and check that all values are transferred as expected – including empty cells.
There is no control of the consistency in the database nor of the links between the three pages
There is no control of the resulting HTML code
People don't read documentation voluntarily! (See www.useit.com)
|
Download images (25 kB): |
|
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: