Excel Report template configuration - SmartPlant Foundation - IM Update 46 - Help - Hexagon

SmartPlant Foundation Help

Language
English
Product
SmartPlant Foundation
Search by Category
Help
SmartPlant Foundation / SDx Version
10
SmartPlant Markup Plus Version
10.0 (2019)
Smart Review Version
2020 (15.0)

Excel templates are used by ad-hoc reports and the Export (Select Template) command. The following procedures explain how to create and import them.

Create this template from Configuration Top so that it can be used by all plants in the site; otherwise you can only use the template with the plant under which it was created. To change the scope to Configuration Top, select Scope Not Set in the Create scope section of the Set Active Scope dialog box. For more information, see Set the active configuration by role.

There are two ways to create an Excel template:

  • Provide an Excel document that did not originate from the Desktop Client.

  • Create an Excel file by exporting data from the Desktop Client.

Before attaching an Excel document to the template, make sure the document is formatted to your specifications. Excel templates are compatible with ad-hoc reports and the Export (Select Template) command.

When creating an Excel template, verify the column order matches either the column order in the Edit Report Definition dialog box that you use to configure your report or the column set used to display data in the list view window. For example, if the list view contains the columns (from left to right) Name, Description, Revision, Version, and Class, the software exports the data in that order. If the Excel template does not match the software, the template columns and exported columns will not match.

SHARED Tip If you have already created an Excel file to use as your template, skip to Load the template file into the Desktop Client.

Create a template from Microsoft Excel

  1. Create a new Excel document.

  2. Add a logo or other information to the Excel document if necessary. Anything you add to the document must be to the right of any potential exported data that will populate the document. For example, if your template populates columns A through G with data, any additional information in the template must be added in column H or after.

  3. Create a macro that is to be run when the file is opened, and name it Initialize_It. For example, you can write a macro to move cells, apply formatting, remove empty rows or columns, or remove duplicate headers and titles.

    • The macro must reside inside a VBA module or it will not run.

    • Refer to the macro used in the Excel file attached to the Excel Default Template document stored.

  4. Save the Excel file as an Excel 97-2003 Workbook (.xls) file or Excel Macro-Enabled Workbook (.xlsm) file.

    The file must be saved with the .xls or .xlsm extension or it cannot be used.

  5. Close the Excel file and skip to Load the template file into the Desktop Client.

If you do not close the file, you will not be able to attach it later.

Create a template from the Desktop Client

  1. Click File > New > Report in the Desktop Client.

  2. Select the report (view definition) and click OK.

  3. Select Excel in the Report output style list, and select one or more items in the Report details list.

  4. Using the Report Definition dialog box, configure the view as you want to view your data. For example, use the Up and Down buttons to organize the properties in the order you want to see them in your report, or use the Caption values to display the column headers as you want to see them.

  5. Click OK to export the results to an Excel file.

    The Excel file opens when the export process completes.

  6. Add a logo or other information to the Excel document if necessary. Anything you add to the document must be to the right of any potential exported data that will populate the document. For example, if your template populates columns A through G with data, any additional information in the template must be added in column H or after.

  7. Create a macro that is to be run when the file is opened, and name it Initialize_It. For example, you can write a macro to move cells, apply formatting, remove empty rows or columns, or remove duplicate headers and titles.

    • The macro must reside inside a VBA module or it will not run.

    • Refer to the macro used in the Excel file attached to the Excel Default Template document.

  8. Save the Excel file as an Excel 97-2003 Workbook (.xls) file or Excel Macro-Enabled Workbook (.xlsm) file.

    The file must be saved with the .xls or .xlsm extension or it cannot be used.

  9. Close the Excel file.

If you do not close the file, you will not be able to attach it later.

Load the template file into the Desktop Client

  1. Select Document Category > Template Documents > Excel Template in the Desktop Client tree window.

  2. Right-click Excel Template, and click New Template Document.

  3. In the New Template Document dialog box, type a Name and Description for the new template.

  4. Select the Item owning group, and click Next. The Attach Files dialog box appears.

    • By default, the local copy of the Excel file is removed from the local folder and stored in the database. Clear the Delete Local Files option to keep a local copy of the file.

    • The first time you open the Attach File dialog box, the Delete local files option is selected by default. Any time you clear the Delete local files option, the selection is stored in the local system to be used as default the next time you access this dialog box. Users can change their preference at any time by selecting this option.

  5. Attach the Excel file created above, and click Finish. The new template document is displayed in the Desktop Client New Items window.

To edit the new report template, you must check out the template, modify it, and then check it back in.