Section 7.2.InfoPath and Excel

7.2. InfoPath and Excel

So what does InfoPath have to do with Excel? The two products aren't directly related, but they are both producers and consumers of XML data. You can gather XML data in InfoPath and then import that data into an Excel list for summary or analysis.

You can also start InfoPath from Excel. In that way, InfoPath forms can be the front-end for gathering XML data used by Excel. This chapter discusses those two approaches.

7.2.1. Link Excel to InfoPath through lists

The Excel and InfoPath products interchange data through lists. Lists may be local XML files, XML files posted at a public location, or they may be shared through SharePoint.

The data collected by InfoPath is often a superset of the information you want to analyze in Excel. For example, you want to create an Excel report on the location and value of assets. If you collect that data using the InfoPath Asset Tracker form (described early in this chapter), you create a worksheet from the form's XML but only include a few of the form's nodes in the worksheet list.

The general steps in using an InfoPath form to collect data for display in Excel are:

1. Create the InfoPath form template.

2. Publish the template.

3. Have users fill out the forms.

4. Optionally, use InfoPath to merge the forms into a single XML file.

5. Open or Import the merged XML data into Excel, creating an XML map for the data.

6. Drag the significant nodes from the Excel XML Source task pane to the worksheet to create a list.

7. Update the list from its data source.

Opening an InfoPath-created XML file in Excel may lock the file and prevent others from editing form data. However, once the list is created you can close the workbook (unlocking the form) and then reopen the workbook to analyze the data. Refreshing the data in the Excel list does not lock the form file.

In this scenario, you usually can't edit form data in Excel and then pass it back to the InfoPath form. The schemas of InfoPath forms usually include lists of lists, which you can't export from Excel. It is possible to create forms that collect data that could be edited in Excel, but you are severely limited in how data items may be related and what data types you can collect. As a practical matter, you should consider getting form data into Excel a read-only operation.

7.2.2. Start InfoPath from Excel

You can display InfoPath forms from Excel using VBA code and the InfoPath ExternalApplication object. InfoPath limits what you can do from VBA for security reasons.

To open an existing form:

1. In Excel VBA, create a reference to the Microsoft Office InfoPath 1.0 type library.

2. Create a new InfoPath.ExternalApplication object variable.

3. Call the Open method on the form.

For example, the following code opens a form from within the workbook's folder:

    pth = ThisWorkbook.Path    Dim ip As New InfoPath.ExternalApplication    ip.Open pth & "\Assets.xml"

To close the form, use the Close method:

    ip.Close pth & "\Assets.xml"

To create a new form, use the NewFromSolution method with the form template:

    ip.NewFromSolution pth & "\Assets.xsn"

To close InfoPath, use the Quit method:

    ip.Quit True

Quit closes all instances of InfoPath, not just the current reference. The True argument tells InfoPath to prompt to save changes before closinga good idea considering that first point.

In SP1, you can also use the Application object from VBA. The Application and ExternalApplication objects are similar, but Application doesn't provide an Open method. Instead, you can open forms through the Application objects XDocuments collection:

    pth = ThisWorkbook.Path    Dim ip As New InfoPath.Application    ip.XDocuments.Open pth & "\Assets.xml"

The Application also provides access to the Windows collection and some InfoPath properties not available from ExternalApplication.

7.2.3. What about...

To learn about	Look here
Excel to InfoPath conversion tools	directory.partners.extranet.microsoft.com/advsearchresults.aspx?listid=23