Using Excel and Excel Macros to Ease Web DevelopmentBy John Lofaro
Like all Web developers I use various tools to accomplish different parts of an application. I have various graphics programs, HTML editors, browsers, and the like. Most of you use these programs, but have you ever considered using Microsoft Excel for producing HTML? I'm not talking about using the "Save As Web Page" feature or the old "Internet Assistant" for Excel. It is the formula writing that I find useful for producing HTML.
Excel's ability to write formulas against data is ideal for coding. For example if you wanted to create a static, hard-coded list for a drop-down menu, you would normally type each item in separately, like:
But if you had the items already in a list you could copy them into a column in Excel (column "A"), and in column "B" write the formula:
The result would be
<option value="Apples">Apples</option>. Simply copy the formula
in column "B" for every list item in column "A". Then you copy the formulas from column "B" into your HTML
code between the select tags!
Because I develop primarily for an intranet, I use Microsoft Access for my database solutions. When I create a Web application I like to name the HTML form elements the same as the fields of the database, this way I reduce potential errors. To save time, and effort, we can create some Excel formulas that produce HTML and VBScript code to create, read, and update these database values. To do this, begin by opening your Microsoft Access table, and create a dummy record (you only need to enter data into one field), or copy an existing record. By copying the record, Access also copies the field names, and this is what we really want. Copy that record and paste it into row 1 in Microsoft Excel. You can now delete the data, (row 2), and should be left only with your field names.
Now we have the record horizontally, but we need the field names to be vertical so we can later paste the formulas into our HTML. To make the field names go from horizontal to vertical we need to copy the record, select "A2", and choose "Paste Special", then "Transpose". Now you should have your field names vertically beginning in "A2" (Excel will not allow you to copy and paste special, transpose in the same cell, so we move to "A2" to accomplish this).
Now let's use the power of Excel's formulas. We can create the elements for a form, code for writing to a database or whatever you need to do with your database fields. To create the form elements, writing to the database, or updating an existing record, the formulas are all similar.
For creating the form elements, use the following formula:
Apples: <input type="text" name="Apples" size="20" >
For writing to the database, use the following forumla:
For writing a SQL statement for updating records, use:
sql=sql & ", Apples='"& request.form("Apples")&"'"
To make the formula easier to understand remember that everything between quotes tells Excel to print that
information in the cell. So
"=sql" will print
=sql in the cell.
The ampersand character (
&) concatenates or merges the values of two things together
(either cells or text). If you wanted the values of column A to be merged with the values of column B, the
formula would be
=A1 & B1. And finally the
CHAR(34) will print a quotation mark
") in the cell.
Now this method would not be very efficient for a small list, unless you had created a macro in Excel to do the work for you and then attached the macro to a button. I have included the code for these macros, which can be found at the bottom of this article. You can copy and paste these into Excel's personal macro workbook. (For more information on using and creating Excel macros, check out this article.)
I hope you find this article helpful. And hopefully this article will allow to look at other applications as potential tools for producing code. Happy Programming!