Consuming Excel Data using InfoPath Database Data Connections (InfoPath team blog)

http://blogs.msdn.com/infopath/archive/2008/08/22/consuming-excel-data-using-infopath-database-data-connections.aspx

Excel Service Settings

Excel Services Settings

Excel services have to be setup properly in SharePoint Central Administration before they are used in SharePoint. New users often forget to set these settings in central administration and they get permissions errors when they try to publish a report in SharePoint or try to access an Excel sheet. For example, one of the errors users get is:

You do not have permissions to open this file on Excel Services.
Make sure that the file is in an Excel Services trusted location and that you have access to the file.

Another error related to Excel Services Settings is:

The file you selected could not be found. Check the spelling of the file name and verify that the location is correct. Make sure that the file has not been renamed, moved, or deleted, that the file is in an Excel Services Trusted Location, and that you have access to the file. If the problem persists, contact your administrator.

Here is how you configure Excel Services in SharePoint Central Administration:

1. Click SSP in SharePoint Central Administration.

2. Click Edit Excel Services settings in "Excel Services Settings" section.

3. Select Process account in "File Access Method".

Scroll down to "External Data" section and enter user's information who has appropriate permissions in SharePoint sites.

Click OK to save changes.

4. Click Trusted File Locations (See figure in Step 2)

5. Click Add Trusted File Location.

6. Add site URL in the Address box and select Windows SharePoint Services in the "Location Type". Depending on your requirements, you can select other options, for example, if you want to access files from a UNC then "Location Type" should be UNC. Check Children trusted check box.

In "External Data" section, select Trusted data connection libraries and embedded (Allow External Data section).

Click OK to save changes.

Now, you can access Excel files in SharePoint.

Working With Excel

Many people know that it is easy to save lists in SharePoint as Excel sheets. For example, if you are in a document library, select "Export to spreadsheet" from the Actions menu to export data to Excel. There are other things that you can do with Excel, for example, you can create charts with Excel, you can print content, etc. Another interesting feature is that you can edit your SharePoint lists in Excel.

1. If you have a document library, select "All Documents" view and click "Edit in Datasheet".
2. Click "Task Pane" to open the task pane. You will notice there are several features that you can take advantage of. Because we are discussing Excel at the moment, so let's stick with it.

3. In the Task Pane, select "Export and Link to Excel". "Opening Query" dialog box will pop up. Click "Open" to open the Excel sheet.
4. Another pop up dialog box "Import Data" will open up. By default, "New workbook" is selected. Click OK to open a new workbook.
5. Now, you can edit any of the row in the list. There are some columns like Modified By, Created By, etc that you can not edit but other fields especially custom fields can be edited and once you are done with editing, select Data > List > Synchronize List to synchronize the list. Your data will synchronize with SharePoint. Easy? Isn't it?

-SSA