How can worksheet be connected with database?

Answer 1

Before proceeding with the following steps, name each range of data that you want to use as a table, and then make sure you then close the workbook in Excel. For information about naming ranges, ask the Office Assistant in Excel aboutdefining names for ranges.

1. In theData Link Propertiesdialog box, click theProvidertab, and then clickMicrosoft OLE DB Provider for ODBC Drivers.
2. Click theConnectiontab, and underSpecify the source of data, clickUse connection string.
3. ClickBuild, and then do one of the following:
   If you already have a data source set up for the database you want to connect to, double-click the name of this data source, and then skip ahead to step 9.
   If you don't have a data source set up, click theFile Data Sourcetab in theSelect Data Sourcedialog box, and then clickNew.
4. In theCreate New Data Sourcedialog box, clickMicrosoft Excel Driver, and then clickNext.
5. Type an identifying name for the data source, clickNext, and then clickFinish.
6. In theODBC Microsoft ExcelSetupdialog box, in theVersionbox, select the version of Excel that was used to create your workbook file.
7. ClickSelect Workbook, and use theSelect Workbookdialog box to locate and select the .xls file.
   You now have a data source set up on your computer to connect to your Excel database. In the future when you connect new PivotTable® lists to this database, you won't have to repeat the setup steps.
8. ClickOK, and in theSelect Data Sourcedialog box, double-click the name of the new data source you just created.
9. ClickOKto return to theData Link Propertiesdialog box.
10. On theConnectiontab, underEnter information to log on to the server, do one of the following:
    If you want users to enter a login name and password to access the data in the PivotTable list when they open your Web page, type the login name in theUser namebox and type the password in thePasswordbox.
    If you don't want users to have to enter a password, type the login name in theUser namebox, type the password in thePasswordbox, and select theAllow saving passwordcheck box.
    If your workbook does not require a password for access and you don't want users to have to log in, select theBlank passwordcheck box, and then select theAllow saving passwordcheck box. Leave theUser namebox blank.
11. If you want to verify that the connection you've set up works, clickTest Connection.
12. ClickOKin theData Link Propertiesdialog box, and if prompted about saving the password, clickYes.
13. In theProperty Toolbox, underUse data from, clickData member, and then select the named range that contains the data you want to display in the PivotTable list.