Display Excel data in InfoPath via SharePoint or XML connections
Learn which 2 methods are available if you want to display data from Excel 2007 as a secondary data source in InfoPath 2007.
If you want to display data from an Excel spreadsheet as a secondary data source in InfoPath, you can use one of two methods:
- Save the Excel spreadsheet as an XML file, and then create a Receive data connection to an XML document in InfoPath to be able to import Excel data into InfoPath.
- Publish and synchronize the data in the Excel spreadsheet as a table to a SharePoint list, and then add an InfoPath data connection to the SharePoint list.
The advantage of using the second method instead of the first method is that the second method allows you to change the data in the Excel spreadsheet after you’ve published it to the SharePoint list, synchronize it with the SharePoint list, and then refresh the data source in InfoPath to see the changes made in the Excel spreadsheet.
With the first method you’d have to replace the XML file in the original InfoPath form template and republish the form template to be able to see the changes made in the Excel spreadsheet. In addition, you’d have to create an XML mapping before you can save the Excel 2007 spreadsheet as an XML file.
In essence, the first method is static, while the second method is dynamic.
The second method makes use of an add-in (Excel 2007 Add-in: Synchronizing Tables with SharePoint Lists), which you can download from Microsoft’s website.
After reading the many "this add-in is useless" comments on the MSDN article mentioned above, I was a bit worried that the add-in would not work like all of the Excel users wrote. But after downloading and installing it, it did work for me, although it is a bit unstable and sometimes pops up errors that it cannot connect to SharePoint, especially when the connection takes long to get established.
Instead of explaining step-by-step how I implemented this solution, I decided to put together this InfoPath video tutorial that shows you how to create a table in Excel, publish it to SharePoint, hook up the SharePoint list in InfoPath, change data in the spreadsheet, synchronize the spreadsheet with the SharePoint list, and then refresh the data source in InfoPath. All this is after you’ve installed the add-in, which is explained in the MSDN article.
Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.