How to: Integrate Access 2007 and Excel 2007 with Office Live Small Business

  • Article

You can export a list view in Microsoft Office Live Small Business into either Microsoft Office Access 2007 or Microsoft Office Excel 2007. You can also create reports in Office Access 2007, or PivotTable views and PivotChart views in Office Excel 2007.

Exporting a list to Office Access 2007

You can export an Office Live Small Business list view to a table in a new or existing Office Access 2007 database. You can export a list view to either a table that shares information with the list or to one that does not. When you export to a table that uses information sharing, you create a link between the list and the table. Changes that you make in one are automatically in the other.

The following table describes three methods for exporting a list view to Office Access 2007:

Action Name Available from Information Sharing Description
Export to Office Access 2007 Datasheet task pane No Create a self-contained, static Office Access 2007 table that doesn't share information with an Office Live Small Business list.
Track this list in Office Access 2007 Datasheet task pane Automatic Create a link between an Office Live Small Business list view and an Office Access 2007 table.
Open with Office Access 2007 Actions list Automatic or No Create a self-contained, static Office Access 2007 table, or create a link between an Office Live Small Business list view and an Office Access 2007 table to share information.

Note

There are columns in some types of lists that are modifiable in the datasheet view in Office Live Small Business, but are read-only in linked Office Access 2007 tables.

To export to Office Access 2007 by using Export to Access
  1. On the list page, on the actions bar, click Actions, and then click Edit in Datasheet.
  2. Click the right side of the datasheet to open the task pane, and then click Export to Access.
  3. Select whether you want to export the list to a table in an existing database or a new database, and then click OK.
  4. Do one of the following:
    • If you selected Existing Database, browse to the location of the database, click the file, and then click Open. You can only use this option if you export to a database that already contains a table that is linked to a list.
    • If you selected New Database, browse to the location where you want to create the file, and then click Create.
  5. Enter your Windows Live ID credentials. If you are not prompted and your list fails to open in Office Access 2007, see the "To troubleshoot exporting to Office Access 2007" section in this topic.
To export to Office Access 2007 by using Open with Access
  1. On the list page, on the actions bar, click Actions, and then click Open with Access.
  2. In the Open in Microsoft Office Access window, click Browse, browse to the location where you want to create the file in the Save As window, and then click Save.
  3. Select Export a copy of the data, and then click OK.
  4. Enter your Windows Live ID credentials. If you are not prompted and your list fails to open in Office Access 2007, see the "To troubleshoot exporting to Office Access 2007" section in this topic.
  5. In Office Access 2007, in the All Tables navigation pane, double-click the list name.
To export to Office Access 2007 by using Track this List
  1. On the list page, on the actions bar, click Actions and then click Edit in Datasheet.
  2. Click the right side of the datasheet to open the task pane, and then click Track this List in Access.
  3. Select whether you want to export the list to a table in an existing database or a new database, and then click OK.
  4. Do one of the following:
    • If you selected Existing Database, browse to the location of the database, click the file, and then click Open. You can only use this option if you export to a database that already contains a table that is linked to a list.
    • If you selected New Database, browse to the location where you want to create the file, and then click Create.
  5. Enter your Windows Live ID credentials. If you are not prompted and your list fails to open in Office Access 2007, see the "To troubleshoot exporting to Office Access 2007" section in this topic.
To update a list with changes made in a linked Office Access 2007 table

Changes that you make in a table that is linked to a list appear automatically in the list. However, if you have the list page open when you make changes in the table, you will not see the changes in the list until the page is refreshed. You can also do the following to refresh the information in the list:

  1. On the list page, on the actions bar, click Actions, and then click Edit in Datasheet.
  2. On the actions bar, click Actions and then click Refresh Data.
To update a linked Office Access 2007 table with changes made in a list

Changes that you make in a list that is linked to a table appear automatically in the table.

  • Open the Office Access 2007 table after making changes in the list, and you will see any changes that you have made.
To reopen a list exported to Office Access 2007 that gives a security warning

When you create a new database by exporting a list view to Office Access 2007, it is automatically saved in the folder you selected. You may encounter a security warning on the message bar each time you open the new database informing you that some of the table's content has been disabled. To enable all of the table's content, do the following:

  1. On the message bar in Office Access 2007, click Options.
  2. In the Microsoft Office Security Options window, click Enable this content, and then click OK.
  3. In the All Tables navigation pane, double-click the list name.

Note

2007 Microsoft Office system programs provide a security setting to warn you when you are opening files with data connections. You can prevent this warning by adding the folder to which you save exported files as a trusted location in Office Access 2007. For more information, see the "To troubleshoot exporting to Access 2007" section in this topic.

To troubleshoot exporting to Office Access 2007

If your list fails to open in Office Access 2007, and you were never prompted to provide your Windows Live ID when exporting your list, you may have multiple IDs stored in your Windows user account. To remove multiple IDs, do the following:

  1. In Control Panel, open User Accounts.
  2. Click the Advanced tab, and then click Manage Passwords.
  3. In the list, select any item with (Passport) in its title, click Remove, and then click OK.
  4. Try exporting the list to Office Access 2007 again.

2007 Microsoft Office system programs provide a security setting to warn you when you are opening files with data connections. To prevent this warning, do the following:

  1. Open Office Access 2007, and then click the Microsoft Office button in the upper left corner.
  2. At the bottom of the window, click Access Options.
  3. On the left navigation bar, click Trust Center, and then click Trust Center Settings.
  4. On the left navigation bar, click Trusted Locations, and then click Add new location.
  5. Click Browse, and in the Browse window, locate the folder in which you want to save your exported lists, click OK, and then click OK again.
  6. In the Trust Center window, click OK, and then in the Access Options window, click OK.

Exporting a list to Office Excel 2007

You can export an Office Live list view to an existing worksheet, a new worksheet, or a new workbook in Office Excel 2007. When you export the list view, you create a query in Office Excel 2007 that retrieves information from the list view. The worksheet is automatically linked to the list view and can draw information from it.

The two methods in the table provide the same export functionality. To enable information sharing in both, you have to make changes to the list in datasheet view. You can then update the linked worksheet by using the refresh external data feature in Office Excel 2007. Though you can update a linked worksheet with changes made in a list, you cannot update a list with changes made in a linked worksheet.

The following table describes two methods for exporting a list view to Office Excel 2007:

Action Name Available from information Sharing Description
Export to Spreadsheet Actions list One way, manual Create a link between an Office Live Small Business list view and an Office Excel 2007 worksheet. You can update the worksheet with changes that you make in your list.
Query list with Office Excel 2007 Datasheet task pane One way, manual Create a link between an Office Live Small Business list view and an Office Excel 2007 worksheet. You can update the worksheet with changes that you make in your list.

Note

Office programs provide a security setting to warn you when you are opening files with data connections. You can prevent this warning by adding the folder to which you save exported files as a trusted location in Excel 2007. For more information, see "To troubleshoot exporting to Excel 2007," below.

To export to Office Excel 2007 by using Export to Spreadsheet
  1. On the list page, on the actions bar, click Actions, and then click Export to Spreadsheet.
  2. Do one of the following:
    • To open the list view directly in Excel 2007:
      1. Click Open.
      2. Click Enable in the Microsoft Office Excel Security Notice window.
      3. Enter your Windows Live ID credentials. If you are not prompted and your list fails to open in Office Excel 2007, see the section "To troubleshoot exporting to Office Excel 2007" in this topic.
      4. In the Import Data window, select whether you want the list to open in an existing worksheet, new worksheet, or new workbook, and then click OK.
    • To save the list view on your computer as a Web Query file that you can open with Office Excel 2007:
      1. Click Save, browse to the location in the Save As window to which you want to download the file, and then click Save.
      2. Click Close. When you open the Web Query file in Excel 2007, you will have to complete steps 2 and 3 under "To open the list view directly in Excel 2007," above.
To export to Office Excel 2007 by using Query list
  1. On the list page, on the actions bar, click Actions, and then click Edit in Datasheet.
  2. Click the right side of the datasheet to open the task pane, and then click Query list with Excel.
  3. In the Microsoft Office Excel Security Notice window, click Enable.
  4. Enter your Windows Live ID credentials. If you are not prompted and your list fails to open in Office Excel 2007, see the "To troubleshoot exporting to Office Excel 2007" section in this topic.
  5. In the Import Data window, select whether you want the list to open in an existing worksheet, new worksheet, or new workbook, and then click OK.
To update a linked Office Excel 2007 worksheet with changes made in a list

Though you can update a linked Office Excel 2007 worksheet with changes made in a list, you cannot update a list with changes made in a linked Office Excel 2007 worksheet.

  • After you have made changes to a list by using the datasheet view in Microsoft Office Live, you can update the linked Office Excel 2007 worksheet by using the refresh external data feature in Excel 2007.
To troubleshoot exporting to Office Excel 2007

If your list fails to open in Office Excel 2007, and you were never prompted to provide your Windows Live ID when exporting your list, you may have multiple IDs stored in your Windows user account. To remove multiple IDs, do the following:

  1. In Control Panel, open User Accounts.
  2. Click the Advanced tab, and then click Manage Passwords.
  3. In the list, select any item with (Passport) in its title, click Remove, and then click OK.
  4. Try exporting the list to Office Excel 2007 again.

System programs in Microsoft Office 2007 provide a security setting to warn you when you are opening files with data connections. To prevent this warning, do the following:

  1. Open Office Excel 2007, and then click the Microsoft Office button in the upper left corner.
  2. At the bottom of the window, click Excel Options.
  3. On the left navigation bar, click Trust Center, and then click Trust Center Settings.
  4. On the left navigation bar, click Trusted Locations, and then click Add new location.
  5. Click Browse, and in the Browse window, locate the folder in which you want to save your exported lists, click OK, and then click OK again.
  6. In the Trust Center window, click OK, and then in the Excel Options window, click OK.

Creating Office Access 2007 reports, PivotCharts, or Excel PivotTable views

If you have exported and linked an Office Live Small Business list to Office Access 2007 or Office Excel 2007, you can create reports, PivotTable, or PivotChart views.

To create an Office Access 2007 report

  1. On the left navigation bar, navigate to the list for which you want to create a report.

  2. On the actions bar, click Actions, and then click Edit in Datasheet.

  3. Click the right edge of the datasheet to open the task pane, and then click Report with Access.

  4. Choose to export the list to an existing database or a new database, and then click OK.

  5. Specify the location of the database.

  6. Office Access 2007 opens a linked table with the exported data and generates a report that displays all the items in the linked list by using AutoReport. For more information about AutoReport, See Office Access 2007 Help.

To create a chart in Office Excel 2007 from an Office Live Small Business list
  1. On the left navigation bar, navigate to the list for which you want to create a chart.
  2. On the actions bar, click Actions, and then click Edit in Datasheet.
  3. Click the right edge of the datasheet to open the task pane.
  4. Click Chart with Excel.
  5. If Excel is already running, open the Web query file when prompted , and then select where you want to import the file. If Excel is not running, it opens and imports the list into a new workbook.
  6. Excel prompts you to create a chart using the Chart Wizard. For more information on Excel charts, see Office Excel 2007 Help.
To create a PivotTable view in Office Excel 2007 from an Office Live Small Business list
  1. On the left navigation bar, navigate to the list for which you want to create a a PivotTable view.
  2. On the actions bar, click Actions, and then click Edit in Datasheet.
  3. Click the right edge of the datasheet to open the task pane.
  4. Click Create Excel PivotTable Report. Office Live Small Business exports the list as a Web query file.
  5. If Office Excel 2007 is already running, open the Web query file when prompted , and then select where you want to import the file. If Excel is not running, it opens and imports the list into a new workbook.
  6. Office Excel 2007 prompts you to create a PivotTable view. For more information about PivotTable views, see Office Excel 2007 Help.
To print a predefined Microsoft Office Access 2007 report

  1. On the left navigation bar, navigate to the Office Access 2007 report you want to print.

  2. On the Action toolbar, click Views, and then select the predefined Office Access 2007 report you want to use.

    Office Access 2007 opens, displaying your Office Live Small Business data in the selected report.

  3. Print the report in Office Access 2007, and then close.

Creating a custom Office Access 2007 view

You can create custom Office Access 2007 views such as data entry forms, reports, and charts, and save them to Office Live Small Business.

To create an Office Access 2007 view

  1. In Office Live Small Business, on the left navigation bar, navigate to the application that contains the list that you want to create a view for.

  2. On the actions bar, click Settings, and then click Create View.

  3. Click Access View. This link appears only if Office Access 2007 is installed on your computer.

  4. Select the type of Office Access 2007 form, report, or chart you want to create, and then click OK. Office Access 2007 opens the Design view.

  5. Use the Design view controls to customize the view.

  6. Save the view, and then click Yes to publish the view in an Office Live Small Business library.

  7. In the Publish to Web Server dialog box, select the location where you want to publish the view, and then click Publish.

    The Save In text box defaults to the Office Live Small Business location where the view was created. Access 2007 closes and you are returned to Office Live Small Business. The name of the view now appears in the View box, and can be used by any employee or person in your organization with access to your Office Live Small Business Home page.