HOW TO: Send Form Results to a Microsoft Excel Worksheet Using FrontPage 2000 (308692)

SUMMARY

You can create a form in Microsoft FrontPage 2000 and have the results from the form written to a Microsoft Excel 2000 workbook. This article gives an example of how to create the workbook and form, and then how to connect them.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site: For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
To create a sample workbook and form, follow these steps.

back to the top

Create the Excel Workbook

1. Start Excel 2000.
2. In cell A1, type First_name.
3. In cell B1, type Last_name.
4. In cell C1, type E_mail.
5. Select cells A1:C1.
6. On the Insert menu, point to Name, and then click Define.
7. Click Add, and then click OK.
8. On the File menu, click Save As.
9. In the File name box, type a name for the file, and then click Save.
10. Quit Excel.

back to the top

Create the Form

1. Start FrontPage 2000.
2. Open the Web that you want to add the form to.
3. On the File menu, point to New, and then click Page.
4. On the General tab, click Normal Page, and then click OK.
5. On the Insert menu, point to Form, and then click Form.
6. On the Insert menu, point to Form, and then click One-Line Text Box. Press ENTER.
7. Repeat step 6 two more times.
8. Click to the left of the first text box, and then type First Name.
9. Double-click the first text box.
10. In the Name box, type First_name, and then click OK.
11. Click to the left of the second text box, and then type Last Name.
12. Double-click the second text box.
13. In the Name box, type Last_name, and then click OK.
14. Click to the left of the third text box, and then type E_mail Address.
15. Double-click the third text box.
16. In the Name box, type E_mail, and then click OK.

back to the top

Import the Workbook into the Web

1. On the File menu, click Import.
2. Click Add File, browse to and click the Excel workbook, and then click Open.
3. Click OK.
4. If you have a folder named fpdb in the Folder List, drag the workbook into the fpdb folder, and then proceed to the next set of steps.
5. In the Folder List, right-click the root folder of the web, and then click New Folder.
6. Type fpdb, and then press ENTER.
7. Drag the workbook to the new folder.

back to the top

Connect the Workbook to the Form

1. On the Tools menu, click Web Settings.
2. On the Database tab, click Add.
3. In the Name box, type a name for this database connection.
4. Click File or folder in current Web, and then click Browse.
5. In the Files of type box, point to the arrow and then click Microsoft Excel Driver.
6. Browse to the fpdb folder, click the workbook, and then click OK.
7. Click Advanced, and then click Add.
8. In the Name box, type ReadOnly.
9. In the Value box, type 0, and then click OK.
10. Click OK twice.
11. Click Verify, and then click OK.
12. Right-click anywhere on the form, and then click Form Properties.
13. Click Send to database, and then click Options.
14. On the Database Results tab in the Database Connection to Use box, point to the arrow, and then click the database connection that you named in step 3.
15. In the Table to hold form results box, point the arrow, and then click first_name.
16. On the Saved Fields tab, click First_name, click Modify, and then click OK.
17. Repeat the previous step for form fields Last_name and E_mail.
18. Click OK twice. If you receive an error message about the file extension of the page, click OK.
19. On the File menu, click Save As.
20. In the Save as type box, point to the arrow, and then click Active Server Pages.
21. Type a name for the file, and then click OK.

back to the top

Test Your Form

1. Publish your Web. The Web server can be Microsoft Personal Web Server or any server with FrontPage extensions.
2. Browse to your form with your Internet browser.
3. Enter text in all the fields in the form.
4. Click Submit.
5. Start Excel.
6. On the File menu, click Open.
7. Browse to the published location of the Web, and then open the workbook.
   
   NOTE: You should see that your submitted data is in the second row of the workbook. A subsequent submission would be entered in the next unused row.

back to the top