Inserting Excel Data with the Database Results Wizard

Most FrontPage users use ASP to display data from a Microsoft Access database or from a SQL Server database, but you can also use ASP to display data from an Excel spreadsheet.

For more information on using the Database Results Wizard, see "Using the Database Results Wizard," p. 710.


Importing the Excel Spreadsheet

The first step in creating an ASP page to display Excel data is to import the Excel spreadsheet into your Web site so that you can connect to it with a database connection. To do that, follow these steps:

  1. Open a Web site or create a new Web site. The Web site must be on a server that supports ASP.

  2. Select File, Import.

  3. Click the Add File button.

  4. Browse to the Excel file, select it, and click Open.

  5. Click OK to import the file.

For more information on the requirements for ASP pages, see "Understanding Active Server Pages (ASP)," p. 680.


Creating the Database Connection

Once the Excel spreadsheet has been imported, you need to create a new database connection that points to the Excel file. The database connection allows you to connect to the Excel file with the Database Results Wizard.

  1. Select Tools, Site Settings.

  2. Click the Database tab.

  3. Click the Add button.

  4. In the Name text box, enter Expenses.

  5. Select the File or Folder in Current Web Site option.

  6. Click Browse.

  7. In the Files of Type dropdown, select Microsoft Excel Driver (*.xls).

  8. Browse to the folder containing the Expenses.xls file as shown in Figure 40.3, select it, and then click OK.

    Figure 40.3. Select the Expenses.xls file to create a database connection to it.

    graphics/40fig03.gif

  9. Click OK in the New Database Connection dialog box to close the dialog.

graphics/troubleshooting_icon.jpg

If the Site Settings option is not enabled, see "Site Settings Is Disabled" in the "Troubleshooting" section of this chapter.


The Site Settings dialog box now shows a new database connection. Notice that the Status column shows a question mark. You can go ahead and click OK at this point, or you can select the connection and click Verify just to make sure that FrontPage can access the file successfully. Once you've verified the connection, FrontPage will display a check mark in the Status column as shown in Figure 40.4. The check mark indicates that FrontPage sees the Excel file; however, it does not indicate that FrontPage can access data inside of it. After you've verified the connection, click OK to add the connection to your Web site.

Figure 40.4. The database connection to the Excel file has been verified.

graphics/40fig04.gif

FrontPage has now added two items to your Web site. It has added an fpdb folder (which is empty) and a global.asa file. The fpdb folder is a restricted folder so that anything put into it cannot be accessed by people browsing to your Web site. Because you wouldn't want people to download your Excel spreadsheet file, you might want to move it into the fpdb folder by dragging and dropping it on the fpdb folder. FrontPage will automatically update the database connection for you.

The global.asa file contains special ASP code that defines your database connection. It also contains other ASP code that FrontPage will use when you create your ASP page connecting to your Excel file.

For more information on the global.asa file and its use in ASP pages, read Active Server Pages 3.0 from Scratch from Que Publishing.


Inserting the Database Results

Now it's time to create a new ASP page and connect to the data inside of the Excel spreadsheet.

  1. Create a new Web page.

  2. Select Insert, Database, Results.

  3. In the first step of the Database Results Wizard, select the ASP option.

  4. Select the Use an Existing Database Connection option and select Expenses in the dropdown as shown in Figure 40.5.

    Figure 40.5. The first step of the Database Results Wizard. You will use the existing database connection you created earlier.

    graphics/40fig05.jpg

  5. Click Next.

  6. Select the Record Source option as shown in Figure 40.6. The only option in the dropdown is Expenses because that is the only named range in the Excel spreadsheet.

    Figure 40.6. You will use the Expenses record source. This record source refers to the named range in your Excel spreadsheet.

    graphics/40fig06.gif

  7. Click Next.

  8. Leave all fields as they are and click Next.

  9. Select the Table One Record per Row option and leave all check boxes checked as shown in Figure 40.7.

    Figure 40.7. Choose the option to display the results in a table. This more closely resembles the original format in Excel.

    graphics/40fig07.gif

  10. Click Next.

  11. Select the Split Records into Groups option and type 5 in the Records per Group text box as shown in Figure 40.8.

    Figure 40.8. It isn't necessary to split into groups for your small spreadsheet, but you might be adding more data later.

    graphics/40fig08.jpg

  12. Click Finish to insert the database results.

Your page should now look like the one in Figure 40.9. Save the page as expenses.asp.

Figure 40.9. The completed page in FrontPage. Notice that FrontPage pulled out the columns from your Excel spreadsheet.

graphics/40fig09.gif

For more information on all the options in the Database Results Wizard, see "Using the Database Results Wizard," p. 709.


graphics/troubleshooting_icon.jpg

If the Results option is not available on the Database menu, see "Cannot Insert Database Results" in the "Troubleshooting" section of this chapter.


Now that you've saved the page, preview it in your browser. When you do, the Web server will connect to your Excel spreadsheet, pull the data from it, and display the data in the browser as shown in Figure 40.10. If you open the original Excel spreadsheet and change the data, the Web page will automatically show the updated data.

Figure 40.10. This Web page was generated dynamically from the data in the Expenses.xls spreadsheet.

graphics/40fig10.gif

TIP

If you're going to edit the Excel spreadsheet that your Web page is connected to, you should make a copy and edit the copy. After you've finished editing, copy the updated file into your Web site and overwrite the existing copy.




Special Edition Using Microsoft Office FrontPage 2003
Special Edition Using Microsoft Office FrontPage 2003
ISBN: 0789729547
EAN: 2147483647
Year: 2003
Pages: 443

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net