Section 1.8.Kick-Start InfoPath

1.8. Kick-Start InfoPath

The list we just created on SharePoint isn't perfect. Some of the items in the topic column are inconsistent and the docstring text could use a bit of a rewrite. You can edit the list directly in SharePoint or Excel, but those tools aren't best suited to editing paragraphs of text or maintaining consistency among entries. A better solution is to use InfoPath to create a data-entry form for the list's XML.

To see how this works:

1. From Excel, export the list as XML data (choose Data XML Export). Name the exported file

   Start InfoPath, choose Design a Form then choose New From XML Document or Schema in the task pane. InfoPath starts the Data Source Wizard.

2. Specify the file you just exported, click Next, then click Finish. InfoPath creates a new empty form template with the schema generated from ExcelObjectList.xml displayed in the task pane.

3. Drag the name, introduced, topic, and docstring elements onto the form as shown in Figure 1-24.

4. Right-click the docstring field on the form and select Text Box Properties. Set the display properties to allow paragraph breaks and wrap text (Figure 1-25).

   Figure 1-24. Creating a form in InfoPath

   
   
   

   Figure 1-25. Setting text properties in InfoPath.

   
   
   

5. Click Preview Form on the InfoPath toolbar. InfoPath displays the XML file (Figure 1-26).

   Figure 1-26. Previewing the XML data in InfoPath

   
   
   

The Preview mode displays what the form will look like for data entry; you can make edits, but you can't save data while previewing. One thing you'll notice right off is that InfoPath underlines words it thinks are misspelled, such as CalloutFormat. If you right-click that word and choose Spelling, you get help in the task pane (see Figure 1-27).

That's kind of a neat idea for a form tool, but you can do standard form tasks as well, such as specifying required fields, validating data, selecting values from a set of predefined entries, etc.

Figure 1-28 shows an improved form that filters the data to display one object at a time. The form uses a secondary data source to supply predefined values for the introduced and topic fields. This ensures that those entries are consistent.

If you save the template at this point, you can open it from Window Explorer to add or change records. When you save the changes, InfoPath writes a new XML file. That new file has the same structure as the original ExcelObjectsList.xml and, in fact, you can re-import it back in to Excel to load the changes made through InfoPath.

---

Note: I purposely skip steps in the design process here. Those steps are covered in Chapter 7.

---

Figure 1-27. InfoPath displays spelling suggestions in the task pane

Figure 1-28. Improved form with filtering, drop-downs, and a secondary data source

Excel and InfoPath share Visual Basic .NET as a common programming language. That means you can automate the editing of a list in InfoPath, then reloading it in Excel through VSTO. To see how this works, close InfoPath and open the project created in "Kick-Start Security and .NET", then follow these steps:

1. In VST0, choose File Add Project New Project and select the InfoPath Form Template, as shown in Figure 1-29.

   Figure 1-29. Creating a new project

   
   
   

2. VSTO starts the Project Wizard. Specify an existing template using the form template you just created, as shown in Figure 1-30.

3. When you click Finish, VSTO copies the template into a new project folder and creates .NET code-behind files for the InfoPath project, shown in Figure 1-31.

4. In the InfoPath project, add a reference to the COM object Microsoft Excel 11.0 Object Library.

5. In the Excel project, add a reference to the COM object Microsoft InfoPath 1.0 Type Library.

Figure 1-30. Selecting a template

Figure 1-31. A VSTO solution containing Excel and InfoPath projects

You can now write Visual Basic .NET code that controls both the workbook and the InfoPath form. For example, you might want to add a command button next to the Excel list that opens the list in InfoPath for editing. The .NET code in the Excel project might look like this:

    ' Create object variable to get events from command button.    Friend WithEvents cmdEditList As MSForms.CommandButton    ' Called when the workbook is opened.    Private Sub ThisWorkbook_Open(  ) Handles ThisWorkbook.Open        ' Initialize command button variable to hook in to events.        cmdEditList = CType(FindControl("cmdEditList"), _        MSForms.CommandButton)    End Sub    Private Sub cmdEditList_Click(  ) Handles cmdEditList.Click        ' Export list (to make sure data is current).        Dim ws As Excel.Worksheet, pth As String, ippth As String, xmlObj = "",_          xmlData As String        pth = ThisWorkbook.Path        ws = ThisWorkbook.Worksheets("Sheet1")        ' Get XML data from list.        ws.ListObjects("List1").XmlMap.ExportXml (xmlObj)        ' Convert to a string (circumvents Excel COM bug).        xmlData = CType(xmlObj, String)        ' Edit the XML to add InfoPath's processing instructions.        Dim ipinst As System.IO.StreamReader        ipinst = System.IO.File.OpenText(pth & "\ipinst.txt")        xmlData = xmlData.Replace("<application>", ipinst.ReadToEnd)        ipinst.Close(  )        ' Write the modified XML file to disk.        Dim xmlFile As New System.IO.StreamWriter(pth & "\ExcelObjectsList.xml",_          False)        xmlFile.Write (xmlData)        xmlFile.Close(  )        ' Start InfoPath and import the data.        Dim ip As New Microsoft.Office.Interop.InfoPath.ExternalApplication        ' Open a new form from the template.        ip.Open (pth & "\ExcelObjectsList.xml")        ' Close this workbook        ThisWorkbook.Close(True)     End Sub

That code is a little complicated, but most of it deals with changing the header of the exported XML to include InfoPath processing instructions (included in the file ipinst.txt) to associate the saved XML file with an InfoPath template. Once you've made those changes, the exported XML is recognized by InfoPath and so can be opened.

Now, if you run the project and click the Edit List button, Excel saves the list as an InfoPath form, then opens that form for editing (Figure 1-32).

Figure 1-32. Edit Excel list using an InfoPath form

Once you're in InfoPath, you probably want to update the Excel list when the form closes. Since Excel can simply re-import data from the InfoPath form file, the .NET code to do that is pretty simple:

    ' In InfoPath project.    Public Sub _Shutdown(  )        ' Start Excel.        Dim xl As New Microsoft.Office.Interop.Excel.Application        Dim lo As Microsoft.Office.Interop.Excel.ListObject        Dim pth As String        ' Make Excel visible.        xl.Visible = True        ' Open the Excel workbook.        pth = System.IO.Path.GetFullPath(".")        wb = xl.Workbooks.Open(pth & "\ch01net.xls")        ' Get the list and import the XML.        lo = wb.Worksheets("Sheet1").ListObjects("List1")        lo.XmlMap.ImportXml(thisXDocument.DOM.xml)    End Sub 

However, InfoPath is very security-aware, so you need to take special steps to ensure that the form is permitted access to Excel and other resources on your computer. To do that you must:

1. Close InfoPath and edit the file manifest.xsf in the InfoPath project to remove the following attribute:

       publishUrl="C:\Documents and Settings\Jeff\My Documents\Programming    Office\DevNote\ch01ipnet\manifest.xsf"

   
   

2. Replace the following attribute:

       trustSetting="automatic"

   
   

3. With this one:

       requireFullTrust="yes"

   
   

4. Run a script to register the form on your machine. I include the script Register.vbs with the sample project and explain it in Chapter 7.

When you run the project, the Excel list is updated after you finish your edits and close InfoPath.