Range From Workbook
The Range From Workbook function is used to import data from an outside workbook to a Placement Cell location in the current workbook.
Settings | |
|---|---|
Directory | The directory where the Excel workbook to import the range from exists. If this is left blank, the Reports Directory of the current project is used (as defined in the Project Explorer). If this is not a full path reference, the setting is used as subfolders of the Reports Directory. This can be a fixed name or cell reference where the cell reference contains the directory. Fixed names can contain XLReporter Name Types like {YYYY}, {MMM}, etc. See the Name Types chapter for the full list of supported keywords |
Workbook Name | The name of the workbook to import the range from. This can be a fixed name or cell reference where the cell reference contains the directory. Fixed names can contain XLReporter Name Types like {YYYY}, {MMM}, etc. If this setting contains a full path, that path is used as the Directory. In addition, the Workbook Name can be specified with a wildcard. This changes the setting from a specific file name to a filter that is used to get the most recent file in the Directory specified. For example, a Workbook Name of Flow*.xlsx would import data from the most recent file in the Directory that starts with Flow and ends with .xlsx. |
Worksheet | The name of the worksheet to import the range from. This can be a fixed name or cell reference where the cell reference contains the worksheet name. Fixed names can contain XLReporter Name Types like {YYYY}, {MMM}, etc. |
Start | If Start is intended to be a reference to a range found on the worksheet, it must be set as a single cell and the value in the cell must be a cell range containing “$”, e.g., “$B$30:$D$30”. Otherwise, this can be set to the entire range to import. |
Direction | This setting defines the Direction in which to expand the Start by in order to determine the entire range of data to import. |
Until | This setting defines how to terminate the detection of the range to import. |
Paste | This setting defines what to paste from the range to the Placement. If the range to import contains formulas to other worksheets it is recommended to use the Values or Values and Formats setting, otherwise the formulas may result in errors in the report workbook. |
Example
To extract summary data existing in a June monthly worksheet in a workbook called MyReport2020.xlsx the following settings would be used:
Placement | |
|---|---|
Cell | $B$4 |
Type | Offset |
Direction | Down |
Offset | dM |
Setting | Value |
|---|---|
Directory | C:\Reports |
Workbook Name | MyReport{YYYY} |
Worksheet | {MMMM} |
Start | $B$30:$D$30 |
Direction | None |
Until | All cells are empty |
Paste | All |