To read the article online, visit http://www.4GuysFromRolla.com/webtech/081001-1.shtml

Using Excel and Excel Macros to Ease Web Development

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:

<select name="selectName">
  <option value="value">textToDisplay</option>
  <option value="value">textToDisplay</option>
  <option value="value">textToDisplay</option>
  ...
</select>

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:

="<option value=" & CHAR(34) & A1 & CHAR(34) & ">" & A1 & "</option>".

Creating the HTML for a static listbox's options.

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.

Copy in one row from the Access database.

Paste using the Transpose option. 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.

Use formulas for generating HTML and VBScript code to create, read, and update database values.

For creating the form elements, use the following formula:

=A2 & ": <input type=" & CHAR(34) & "text" & CHAR(34) & " name=" & CHAR(34) & A2 & CHAR(34) & " size=" & CHAR(34) & "20" & CHAR(34) & " > "

This produces: Apples: <input type="text" name="Apples" size="20" >

For writing to the database, use the following forumla:

= "rs(" & CHAR(34) & A2 & CHAR(34) & ")=request.form(" & CHAR(34) & A2 & CHAR(34) & ")"

This produces: rs("Apples")=request.form("Apples").

For writing a SQL statement for updating records, use:

="sql=sql & "", " & A2 & "='"" & request.form(" & CHAR(34) & A2 & CHAR(34) & ")" & "&""'"""

This produces: 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!

  • By John Lofaro


    Macro Code

    Sub ASPForm2DB()
    '
    ' db2form Macro
    
      ActiveCell.FormulaR1C1 = _
          "=""rs("" & CHAR(34) &RC[-1]&CHAR(34) &"")=request.form("" & _
          CHAR(34) &RC[-1]&CHAR(34) &"")"""
      ActiveCell.Offset(1, 0).Range("A1").Select
    End Sub
    
    
    Sub ASPDB2Form()
    '
    ' ASPDB2Form Macro
    
      ActiveCell.FormulaR1C1 = _
               "=RC[-1]&"": <input type="" & CHAR(34) &""text"" & _
               CHAR(34) & "" name="" &CHAR(34) & RC[-1]& CHAR(34) & _
               "" size="" &CHAR(34)& ""20"" & CHAR(34) & "" > """
      ActiveCell.Offset(1, 0).Range("A1").Select
    End Sub
    
    
    Sub ASPDB2DropDown()
      ActiveCell.FormulaR1C1 = _
               "=""<option value=""&CHAR(34)&RC[-1]&CHAR(34)& _
               "">""&RC[-1]&""</option>"""
      ActiveCell.Offset(1, 0).Range("A1").Select
    End Sub
    
    
    Sub ASPSQLUPDATE()
    '
    ' ASPSQLUPDATE Macro
    
      ActiveCell.FormulaR1C1 = _
                "=""sql=sql & """", ""&RC[-1]&""='""""& _
                request.form(""&char(34)&RC[-1]&char(34)& _
                "")"" & ""&""""'"""""""
      ActiveCell.Offset(1, 0).Range("A1").Select
    End Sub
    


  • Article Information
    Article Title: Using Excel and Excel Macros to Ease Web Development
    Article Author: John Lofaro
    Published Date: Friday, August 10, 2001
    Article URL: http://www.4GuysFromRolla.com/webtech/081001-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers