|
|
 |
|
|
Published: Friday, August 10, 2001 By 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 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 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:
This produces:
For writing to the database, use the following forumla:
This produces: For writing a SQL statement for updating records, use:
This produces:
To make the formula easier to understand remember that everything between quotes tells Excel to print that
information in the cell. So 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!
Macro Code
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||
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).