Excel Spreadsheet

The Excel Spreadsheet source connection will allow a user to select a specific range or worksheet within an Excel workbook as the source of Account Line data. The connection uses the Microsoft Jet Engine to load and process the document. As a result, the document can not be open by the user with write permissions when the application attempts to open the document. An button is provided on the first page to open the workbook in read-only mode.

Data Region Page

•Data Region Name: On this page, you must select the worksheet or range from which the source data will be imported. Expanding the drop down box will query the pages on the Excel workbook and list them for selection. As explained on the page, the data region name can include a specific range within the worksheet.

oSheet1$: reads all data from the range of defined cells in the worksheet.

oRegionName: Reads all data from the workbook level named range "RegionName"

oSheet1$RegionName: Reads all data from the worksheet level named range "RegionName"

oSheet1$B5:G10: Reads data from Sheet1 in the range B5 through G10.

•First Row Has Column Names: Selecting this option will tell pasTransfer whether column names are included within the first row of the specified data region. If column names exist, they will be used for mapping macros on the next page.

•Preview: This will display the region specified in Data Region Name as a table to verify the correct range has been selected. It will not display all rows, but only a sample size.

•Open In Excel: Open the selected Excel workbook in read-only mode. No changes can be made to the document, but the workbook can remain open while selecting a data region and macros.

Data Record Column Macros Page

The Data Record Column Macros Page will display the columns within the specified data region and provide a means of mapping columns to a Account Line. Clicking and dragging an item from the list of columns to a macro-enabled text box or grid will create a macro to reference the column.

•Preview: This will display the region specified in Data Region Name as a table to verify the correct range has been selected. It will not display all rows, but only a sample size.

•Reset: Clears the contents of all controls which contain column macros.

General Tab

•Account Code: The identifying account code for the account. This is a macro-enabled field.

•Account Type: The type of the account. This is a macro-enabled field.

•Description: The description for an account. This is a macro-enabled field.

•Currency: The currency for an account. This is a macro-enabled field.

•Active: The active status for the account. This is a macro-enabled field.

•Site: The site for the account. This is a macro-enabled field.

Attributes Tab

Any values from the source file can be made into attributes and mapped later on the destination transformation. Click and drag a column to the attribute data grid. The default name of the attribute will be the name of the column. To delete a row, select the row-header and press the delete button.

Send comments on this topic.