Creating an online survey using Excel and Frontpage

In this article we'll explain how to create an online survey form. We'll build the form in Excel, then convert it to web format with SpreadsheetConverter. When the user submits the form, we'll use the Frontpage Server Extensions to forward the survey response to an e-mail Inbox.

This example uses the Frontpage Extensions to send a survey form to an e-mail Inbox. If you want to, you can use SpreadsheetConverter's own built-in form-to-Inbox support instead.

We'll be happy to tell you a lot more about SpreadsheetConverter, our famous Excel-to-html web authoring product. Why not begin by sending us one of your own spreadsheets, and we'll send it back as a web page? It's free and it really shows you what we can do for you.

If you want to learn more about SpreadsheetConverter, we suggest you start at the beginning.

The final web page
Web forms are commonly used to obtain information from users. Users fill in or select values for a series of questions and then, when they are satisfied with their answers, they click on a button to submit the information.

Here is the final web form that we'll use in our survey.



The final web page

The example survey form that we'll be creating in this article allows users to provide feedback on a fictitious training course. Each time someone fills out the survey form and clicks on the 'Submit' button, the web page automatically generates and sends an email containing a summary of the users responses. To take a look at a finished version of the web page click here. 

During this article we will be using the following software: 

• MS Excel 97, 2000, 2002, 2003.
• SpreadsheetConverter HTML.
• A web server with MS FrontPage server extensions, for example IIS on Windows or Apache on Linux.
• MS Frontpage.

The article consists of 3 parts 

• Creating the original survey spreadsheet in Excel.
• Using SpreadsheetConverter to create a web page from the spreadsheet.
• Setting up the web page to be used with MS FrontPage Server Extensions.

Creating the original survey spreadsheet in Excel

The raw spreadsheet before we added formatting

The survey spreadsheet will consist of two columns that will be displayed on the resulting web page, one column containing the questions of the survey, and the other column containing the input cells for the user’s responses. Extra columns are also used in the example survey spreadsheet for the options that will be displayed in dropdown list boxes. 

Note! The original survey spreadsheet contains no formulas. We do not need formulas for this example. 

The raw spreadsheet without the formatting can be found here. 

STEP1: ENTER THE HEADINGS AND QUESTIONS

Both the headings and the questions that make up the survey form should be entered as text into the spreadsheet. 

Use a new spreadsheet cell for each separate heading or question. 

STEP 2: CREATE THE CELLS FOR THE ANSWERS TO THE QUESTIONS

The cells that you want the user to use to enter their responses to the survey questions should all be marked by setting the background color to an arbitrary unique color. All cells must have the same color, for example in the survey spreadsheet we’ve used bright yellow. 

Next, each of the marked response cells should be defined as input cells. The choice of which kind of input cell to use in each case will depend upon the format of the response required from the user. The three basic sorts of input cells are: Textbox cells, where the user enters a free text response; Dropdown list cells, providing a user with a list of choices; and Checkbox cells, allowing the user to select between two values (true/false). Instructions for adding each of these input cells can be found below: 

CREATING TEXTBOX INPUT CELLS

For responses where the user will simply enter some text, no special action is required in the spreadsheet. 

CREATING DROPDOWN LIST INPUT CELLS

To create a Dropdown list input cell:

Tip: Add an extra element first in the list, so that you can recognize that the user didn't select anything, for example, instead of having just the two options: Yes No use three options instead: -- Select Yes or No -- Yes No

1. Write each entry you want to appear in the list in a separate cell alongside the cell that you want to use for the dropdown list.
2. Create a named range for the list of choices by first highlighting the cells, and then selectingName/Define… from the Insert menu (using a named range makes it easier to add or remove entries from the list).
3. Select the cell where the dropdown list should appear and select Validation… from theData menu.
4. In the Data Validation dialog, select List in the Allow: field and then set the Source: to equal the named range that you created in stage 2 above (e.g. =your_named_range).

CREATING CHECKBOX INPUT CELLS

In order to create a Checkbox input cell simply type the text ‘FALSE’ into a cell, and SpreadsheetConverter will create an unchecked checkbox. 

STEP 3: HIDE ROWS AND COLUMNS

The final step in creating the survey spreadsheet is to hide the areas of the spreadsheet that shouldn't be visible on the webpage. This can be achieved simply: 

Hide the columns

1. Selecting the rows/columns that should be hidden.
2. Right-click on the selected rows/columns and then choose Hide from the list of options.

Using SpreadsheetConverter to create a web page from the spreadsheet

Now that we have created the survey spreadsheet the next step is to use SpreadsheetConverter to create a web page from the spreadsheet. 

STEP 1: START UP SPREADSHEETCONVERTER

When SpreadsheetConverter is installed it adds an extra menu to Excel. The new menu is placed to the right of the Help menu: 

Start SpreadsheetConverter by selecting it from the Excel menu

1. From the SpreadsheetConverter menu select the Convert to HTML option.
2. The SpreadsheetConverter wizard will now start.

Note! If you have paid for a SpreadsheetConverter license, the first page on the wizard is where you can enter the license key. If you do not have a key, you can test the program for 30 days. The only restriction is that only the first 20 rows of the spreadsheet will be visible on the web page, so everything that you want the user to see must be placed on the top 20 lines of the spreadsheet. This does not apply to formulas or tables, which will still be used regardless of where they are placed in the workbook. 

STEP 2: SELECT THE INPUT CELLS

The input cells are the spreadsheet cells that should be converted into input fields on the web page. Input fields are the editable regions of a web page that are used by the user to enter values (in this example the values will be the responses to the survey questions). There are two methods for selecting input cells in the SpreadsheetConverter wizard: 

Select which cells the user should be able to edit

1. Automatic detection: With this option all cells used in formulas found in the workbook will automatically be set as input fields in the web page.
2. Coloring: This option allows the manual selection of input cells. All cells in the spreadsheet with a certain background color, regardless of whether they are used in formulas, will be set as input fields in the web page.

Since the example survey spreadsheet that we created contains no formulas, the Coloring option must be chosen: 

Input cells are yellow

1. Select the Using coloring option and click on the … button.
2. In the Select coloring scheme dialog that appears select the color that was used to highlight input cells in the spreadsheet (in our survey example this was bright yellow).
3. Click on the OK button to confirm the selection.
4. Click on the Next> button to proceed to next step of the wizard.

STEP 3: ADJUST THE LAYOUT OF THE WEB PAGE

The layout of the generated web page can be adjusted by changing a number of options found in theOverall Layout step of the SpreadsheetConverter wizard. To modify the standard list of options: 

Fine-tune the look-and-feel of the web page

1. Click on the … button alongside the option labeled Spreadsheet layout.
2. A dialog box will appear containing a whole range of options used to adjust the appearance of the generated web page.
3. In this example we will keep the standard set of layout options. So click on OK button to accept the present set of layout options.
4. Click on the Next> button to move to the next step of the wizard.

Use the defaults for the layout

STEP 4: ADJUST THE FORMATTING

The Cell Formatting step of the SpreadsheetConverter wizard allows you to specify which parts of the spreadsheets cell formatting should be preserved in the generated web page.In the survey example we need to remove the yellow coloring used to highlight the input cells: 

Keep all formatting

1. Click on the ... button alongside the Keep patterns option.
2. Check the Ignore background color for input cells and make them white option in the dialog box that appears and click on OK.
3. Click on the Next> button to move to the next step of the wizard.

Remove the yellow by saying that input cells should be white

What part of the formatting should be kept? 

Most users will want to keep the majority of the spreadsheets formatting. However, a web developer, who wishes to make the survey an integrated part of a complex web page, may choose to ignore all of the spreadsheet formatting and instead add the formatting at a later stage using tools like Dreamweaver or FrontPage. 

The web page before we added formatting

STEP 5: NAME THE WEB PAGE

SpreadsheetConverter now has all of the information it requires to convert the spreadsheet into a web page. However, before the conversion can be carried out a name and location must be specified for the new web page: 

1. Click on the … button.
2. In the Save As dialogue that appears, select a suitable name and location for the new web page, and then click on the Save button.
3. Click on the Next> button to move to the final step of the wizard.

STEP 6: LET THE WIZARD GENERATE THE WEB PAGE

The final step of the SpreadsheetConverter wizard is to convert the spreadsheet and generate the new web page: 

1. Click on the Finish button to begin the creation of the new web page.
2. The conversion process will now begin, and after a few seconds a browser window will appear displaying the newly generated survey web page.

Setting up the web page to be used with MS FrontPage Server Extensions

Now that we’ve created the survey web page the final step is to set up the file to be used in MS FrontPage. However, before moving onto using FrontPage, we’ve made some improvements to the layout of the original survey spreadsheet. The new spreadsheet can be found here. 

Leave space for submit button

Note! Notice that some extra space has been created at the bottom of the spreadsheet for the addition of a submit-button. 

Select File-Import

STEP 1: IMPORT THE WEB PAGE INTO FRONTPAGE

1. Start up MS FrontPage and open an existing Web.
2. Select the Import option from the File menu.
3. In the Import dialog that appears click on the Add File… button.
4. Select the web page that you created using SpreadsheetConverter in the previous section of this article.
5. Click on the OK button to add the page to your web site.

Select the file created by SpreadsheetConverter

If the web contains FrontPage themes and shared borders, then use the following instructions to apply them: 

1. Open the newly inserted page. Note that it doesn’t contain any navigation bars.
2. Select Theme… from the Format menu and then select the required theme. Click on OK.
3. Next, choose Shared Borders from the Format menu and select the appropriate borders to apply to the selected page. Click on OK.
4. From the View menu choose the Navigation option. Drag-and-drop the inserted page from the folder list into the appropriate location in the navigation view.
5. Right-click on the page that you just added to the navigation view and ensure that there is a tick alongside the option Included in Navigation bar.

Your new page should now look like the other pages in your web site and should also be included within the web’s navigation structure. 

Right click in the form and select 'From Properties...'

STEP 2: ADD AN EMAIL ADDRESS TO THE WEB PAGE FORM

Each time a user completes the survey form, the set of answers entered in the survey must be sent to a specified email address. Using MS FrontPage server extensions the addition of an email address to the survey is achieved very easily: 

1. Open the survey page in FrontPage.
2. Right-click on the web page somewhere inside the form and select Form Properties....
3. Under Where to store the results, select the Send to: option and then enter the appropriate email address in the box labelled Email Address:. Click on OK.

Select 'Send to' and enter email address

The FrontPage server extensions were originally developed and defined by Micosoft, but are also supported by Apache, a popular web server currently boasting 55% of the market share, (seehttp://www.netcraft.com/Survey/index-200202.html). The advantage of using the FrontPage server extensions is that you do not need to do any web server programming yourself. 

Note! You do not need to have a FrontPage license to use the FrontPage server extensions. 

Insert button

STEP 3: ADD A SUBMIT-BUTTON

The final stage in setting up the new survey form in FrontPage is to add a ‘Submit’ button. The submit button will be used by the user to commit their responses to the survey questions. Each time the user clicks on the submit button an email will be sent to the email address that was specified in Step 3. In order to add a Submit button to the survey form perform the following: 

Right-click on button and select 'Form Field Properties...'

1. From the Insert menu select Form and then Push Button.
2. A new button should now appear on the web page.
3. Open up the properties of the new button by right clicking on the button and selecting Form Field Properties….
4. Set the Button type: option to Submit and click on OK.

The survey form is now ready to be saved and published. 

Set button type to submit

STEP 4: TEST THE SURVEY WEB PAGE

Now that the page is fully configured, save the page and then publish the page to your web server.To test that the new page works correctly do the following: 

1. Start up your browser.
2. Enter the address of your new page (starts with "http://").
3. Fill in the survey form with some test responses to the questions.
4. Click on the Submit button.
5. A page entitled Form Confirmation should now appear confirming the details that were sent in the email.

The standard confirmation shows all values entered

STEP 5: CREATE A NICER LOOKING CONFIRMATION PAGE

In Step 4 above saw how the FrontPage server extensions automatically generate a confirmation page each time the survey form is used. This page can easily be replaced with your own confirmation page. To replace the automatic confirmation page with your own page, carry out the following: 

1. Open the survey web page.
2. Right click on the survey form and select Form Properties....
3. Click on the Opions... button and the Options for saving the results of form dialog should appear.
4. Select the tab called Confirmation Page... and enter the link to your own confirmation page in the box marked URL of confirmation page (optional):. Click OK.
5. Click on the OK button to confirm the selection.

Now, when a user clicks on the submit button, the new page will be shown. 

Open form field properties and select confirmation page

A customized thank you page

Each time a user fills in the form, and clicks on the 'Submit' an email will be generated sent to the email address entered in step 2 above. The email will contain all of the information from the survey. An example email can be seen below: 

name: Klas
course: Excel XP Basic
course_rating: Great!
anything_new: Yes
instructor: Darren
instructor_rating: Great, I learned so much.
explaination: Yes
documentation: Yes
p1A28: More cookies. please.
p1A29:
p1A30:
B1: Submit

CONCLUSION

I hope you see how easy it is to use SpreadsheetConverter together with the FrontPage server extensions to create online forms. The steps above can be repeated for creating many different types of forms. In this case the original spreadsheet didn't contain any formulas, but if it had, the steps involved would be exactly the same. 

Using the same technique, you can create: 

• order forms for your small business
• reservation forms
• A financial advisor

Good luck! Do not hesitate to contact support if you have any problems.