Convert an excel workbook to an infopath form template

Some organizations use Microsoft Office Excel workbooks as forms to collect data. These workbooks usually include blank cells for users to enter data. You can convert a workbook to a Microsoft Office InfoPath form template by using the Import Wizard in InfoPath. By converting a workbook to a form template, your users can benefit from InfoPath features such as schema validation, dynamic controls such as repeating sections, and business logic such as data validation. In addition, you can make your form template available to a wider audience by creating a browser-enabled form template. To create a browser-enabled form template, you must publish a browser-compatible form template to a server that is running InfoPath Forms Services. Forms based on browser-enabled form templates can be filled out by using a Web browser.

When you convert an Excel workbook to an InfoPath form template by using the default setting in the Import Wizard, the resulting form template contains the layout of the Excel workbook. In addition, cells in the Excel workbook that meet certain conditions are automatically converted to text box controls that users can enter data into. For example, if a cell is formatted to show a border on all sides, then that cell is converted to a text box control in the resulting form template. If you decide not to use the default setting in the Import Wizard, you can choose to include only the layout when you import the Excel workbook or to convert only certain types of cells to controls.

This article explains the basic concept and procedure for converting an Excel workbook to an InfoPath form template, including the features and settings that are not supported in the conversion process.

In this article

• About converting an Excel workbook to an InfoPath form template

• Features and settings that are not fully supported during conversion

• Convert an Excel workbook to an InfoPath form template

About converting an Excel workbook to an InfoPath form template

When you convert an Excel workbook to an InfoPath form template, the workbook is used like a blueprint to create a new form template. The table structure of the workbook is recreated as a layout table in the form template. If you choose to include cells that are used to collect data when you convert the workbook, text box controls are added to the form template in the layout table cells that correspond to the location of the fields in the workbook. The size and position of supported cells, cell borders and shading, and whether cells are merged or split are preserved in the resulting form template.

If your workbook contains several worksheets, the data and formatting on the first worksheet are added to the default view in the new form template, and the additional worksheets are converted to corresponding views in the form template. The titles of each additional view match the titles of the worksheets.

To better understand the relationship between a workbook that you convert by using the InfoPath Import Wizard and the resulting form template, imagine that you are an insurance agent with a workbook named Claims.xls that contains two worksheets. The first worksheet is named Home and the second is named Automobile. Among the columns on each worksheet are ones with the column headers Type, Description, Loan Amount, and Purchase Price. The Loan Amount and Purchase Price columns on each worksheet are formatted to use a currency data type. All column headers are formatted as bold blue text. The Home worksheet contains a formula to calculate an amortization schedule. A single cell, with border formatting applied on all sides, is used to enter a customer's name.

If you use the the Import Wizard to convert Claims.xls to a form template, you create a form template called Claims.xsn.

This new form template contains two views. The default view, called Home (default), contains the layout and formatting from the Home worksheet. The second view, called Automobile, contains the layout and formatting from the Automobile worksheet. Each view contains a table with the column headers Type, Description, Loan Amount, and Purchase Price, formatted with bold blue text, just as in the original workbook. However, depending on the settings that you choose in the Import Wizard, some features may differ in the resulting form template:

• If you use the Import Wizard to convert Claims.xls to a form template by preserving the layout only    The resulting form template contains a layout table that corresponds to the table in the Excel workbook. The form template does not contain any text box controls.

• If you use the Import Wizard to import Claims.xls both by preserving the layout and by converting cells to controls     The resulting form template contains a repeating table. Cells that meet certain conditions, such as the cell with border formatting applied on all sides, and the cells that contain or reference a formula for calculating an amortization schedule, are converted to text box controls.

  Note: Although cells that contain or reference formulas are converted to text box controls, the formulas are not preserved in the resulting form template. You must recreate the functionality that the formula provided by using the appropriate features in InfoPath.

Top of Page

Features and settings that are not fully supported during conversion

Some settings and formatting in Excel workbooks are not supported by InfoPath. When you convert a workbook that contains such settings, the resulting InfoPath form template will not contain those features or settings. For example, if your workbook has a header that contains an image, the image will not be preserved in the resulting InfoPath form template because InfoPath does not support images in headers and footers.

The following is a list of features and settings that are not preserved when you convert an Excel workbook to an InfoPath form template:

• Cells with "shrink to fit" formatting

• Cell background images

• Cell background patterns

• Cell gradients

• Conditional formatting

• Data validation

• Formulas

• Macros

• Print settings (including A4 paper resizing, black and white, center on page, first page number, page order, pictures, print gridlines, print quality, row and column headings, rows and columns on every page, scaling, and set print area)

• Vertical text alignment

• Rows that are narrower than the default font height of 10pt

• WordArt

The following list explains the features and settings that are partially supported when you convert an Excel workbook to an InfoPath form template:

Cells with data formatting     Styles or colors applied to cells with data formatting are not converted. For example, if a cell is formatted to display negative numbers as red text, the number value is converted, but the red text formatting is not.

Hyperlinks that reference unsupported protocols     All hyperlinks are converted, but if the hyperlink uses a protocol other than http:, https:, ftp:, or mailto:, the hyperlink will not work when a user clicks the link.

Header and footer alignment and formatting     A header or footer in an Excel workbook can contain a left, middle, and right section. These are concatenated upon import to InfoPath. For example, a workbook with a left header containing the name "Wendy Wheeler," a middle header containing the title "Status Report" and a right header containing date "October 13, 2007" would be converted to an InfoPath form template containing a single header with the following text: "Wendy WheelerStatus ReportOctober 13, 2007." If only one section in the workbook's header or footer contains text, the resulting text in the form template will be aligned accordingly. For example, if only the right section of the header contains text in the workbook, then the corresponding text in the form template will be right aligned. Otherwise, all header or footer text is aligned left when it is imported. The font setting applied to the first header or footer section in the workbook is applied to the entire header or footer in the resulting InfoPath form template.

Font conversion    Although InfoPath imports the fonts from a workbook when creating a new form template, if the fonts in the workbook are not available on the computer where you perform the import, alternate fonts will automatically be selected for the form template.

Implicitly merged cells     In Excel, if you type more text than will fit in the current cell, the text will appear on top of the subsequent cells as if the cells had been merged. InfoPath does not support this feature. If a converted cell contains more text than the cell width can accommodate, the text will wrap to the next line in InfoPath. To prevent this, before you import your workbook, select the cell and as many subsequent cells as are necessary to contain the text, and merge the cells so that the text fits within the merged cell.

PivotTable reports     PivotTable reports are converted as layout tables.

Very large tables     InfoPath supports tables that are up to 63 columns wide and 999 rows long. If an Excel workbook exceeds these limits, only the first 63 columns and 999 rows will be converted.

Note: Some Excel workbooks cannot be opened without a password. If this is the case with your workbook, you may not be able to successfully import it into InfoPath. To fix the problem, try removing the password requirement for the workbook before you import it. In addition, some workbooks are designed to prevent users from moving, deleting, hiding, or changing the name of worksheets, and from changing other structural elements in the workbook. If this is the case with your worksheet, you may not be able to successfully import it into InfoPath. To fix this problem, try removing the structural editing restrictions for your workbook before you import it.

Top of Page

Convert an Excel workbook to an InfoPath form template

1. On the File menu, click Import Form.

2. In the Import Wizard dialog box, click InfoPath importer for Excel workbooks, and then click Next.

3. Click Browse.

4. Locate and click the Excel workbook that you want to convert, and then click Open.

5. To change the default import behavior, click Options, and then select the options that you want.

6. Click Finish.

   Note: Depending on the layout and complexity of your Excel workbook, some layout elements or controls might not appear as you expected in the resulting InfoPath form template. As a result, you may need to make adjustments to your new form template after you convert it.

Top of Page