Adding and Executing Excel Reports 9.3

From SysCAD Documentation
Jump to navigation Jump to search

Navigation: User Guide -> Reports -> Excel Reports -> Adding and Executing Excel Reports

Excel Report Summary Adding & Executing Report Get Tag Report Tag Select Report Set Tag Report Trend Report Quick View Data Report

Introduction

Once the SysCAD Report is set up in MS Excel using the formats described in the other sections (refer to Related Links at top of page), the report can be called from SysCAD using the Excel Automation dialog window. This can be accessed as follows:

Command Button Reportbutton.png
Command Path Tools - Reports
Short Cut Key Ctrl+R

Excel Automation: Export Reports and Import Data Dialog Box

The different tabs on the SysCAD Report dialog box are described in the following sections.

Export Reports Tab

Use this Tab to Transfer SysCAD Data to MS Excel.

Export Report Tab 9.3.jpg

Columns:

  • Filename: The name of the Excel Workbook for the report.
  • Name: The name of the SysCAD report. Please note that a * represents all reports in the specified Excel Workbook.
  • Folder: The path location of the specified Excel Workbook.

Buttons:

  • Add: Used to add a new report to the list. (See heading Adding an Excel Report to SysCAD for more information.)
  • Copy: Adds another entry into the Dialog box based on an existing entry (currently selected report). Only valid if one or more entry has been added.
  • Remove: Used to remove the currently selected report from the list.
  • Edit...: Used to change Report name or path of report for the currently selected file. This does not open the file for editing purposes.
  • Edit in Excel: Opens the currently selected report in MS Excel for editing.
  • Check Tags: Upon pressing this button the user must choose between Check Tags and Check and Fix Tags.
    The Check Tags function reports on feedback worksheet "CheckTag Errs" any old tags found that need upgrading as well as reporting any model tags (eg Pipes, Units, Controllers) that are not found.
    The Check and Fix Tags function works the same as Check Tags and takes the additional step of correcting the "old" tags found in the spreadsheet.
  • Info Report: Upon pressing this button, the user must choose between automatically creating a General Information Report or a Species Information Report on the Excel Workbook of the currently selected report.
    Note: Both of the above report types do not require the user to add any SysCAD key words to the Excel report. However, if no existing SysCAD report workbook has been set up, save a blank MS Excel spreadsheet, then add it to the dialog box to activate this button.
  • Generate: Generate a Get Tag report. This button will call the Excel automation functions to transfer data from SysCAD to Excel. NOTE: data is not linked between the two software packages, transfer will only happen once whenever this button is pressed.
  • Generate All: Generates ALL Get Tag reports in the dialog box. This button will call the Excel automation functions to transfer data from SysCAD to Excel. NOTE: data is not linked between the two software packages, transfer will only happen once whenever this button is pressed.

Import Data Tab

Use this Tab to Transfer Data from MS Excel to SysCAD.

Excel Import Data Tab 9.3.jpg

Columns:

  • Filename: The name of the Excel Workbook for the report.
  • Name: The name of the SysCAD report. Please note that a * represents all reports in the specified Excel Workbook.
  • Folder: The path location of the specified Excel Workbook.

Buttons:

  • Add: Used to add a new report to the list. (See heading Adding an Excel Report to SysCAD for more information.)
  • Copy: Adds another entry into the Dialog box based on an existing entry (currently selected report). Only valid if one or more entry has been added.
  • Remove: Used to remove the currently selected report from the list.
  • Edit...: Used to change Report name and path for the currently selected file. This does not open the file for editing purposes.
  • Edit in Excel: Opens the currently selected report in MS Excel for editing.
  • Check Tags: Upon pressing this button the user must choose between Check Tags and Check and Fix Tags.
    The Check Tags function reports on feedback worksheet "CheckTag Errs" any old tags found that need upgrading as well as reporting any model tags (eg Pipes, Units, Controllers) that are not found.
    The Check and Fix Tags function works the same as Check Tags and takes the additional step of correcting the "old" tags found in the spreadsheet.
  • Process: This button will call the Excel automation functions to transfer data from Excel to SysCAD. NOTE: data is not linked between the two software packages, transfer will only happen once whenever this button is pressed.
  • Process All: Processes ALL the reports in the Dialog box. This button will call the Excel automation functions to transfer data from Excel to SysCAD. NOTE: data is not linked between the two software packages, transfer will only happen once whenever this button is pressed.

Trend Reports Tab

Use this Tab to generate Trend Reports in MS Excel.

Trend Report Tab 9.3.jpg

Columns:

  • Filename: The name of the Excel Workbook for the report.
  • Name: The name of the SysCAD report. Please note that a * represents all reports in the specified Excel Workbook.
  • Folder: The path location of the specified Excel Workbook.

Buttons:

  • Add: Used to add a new report to the list. (See heading Adding an Excel Report to SysCAD for more information.)
  • Copy: Adds another entry into the Dialog box based on an existing entry (currently selected report). Only valid if one or more entry has been added.
  • Remove: Used to remove the currently selected report from the list.
  • Edit...: Used to change Report name or path of report for the currently selected file. This does not open the file for editing purposes.
  • Edit in Excel: Opens the currently selected report in MS Excel for editing.
  • Check Tags: Upon pressing this button the user must choose between Check Tags and Check and Fix Tags.
    The Check Tags function reports on feedback worksheet "CheckTag Errs" any old tags found that need upgrading as well as reporting any model tags (eg Pipes, Units, Controllers) that are not found.
    The Check and Fix Tags function works the same as Check Tags and takes the additional step of correcting the "old" tags found in the spreadsheet.
  • Info Report: Upon pressing this button, the user must choose between automatically creating a General Information Report or a Species Information Report on the Excel Workbook of the currently selected report.
    Note: Both of the above report types do not require the user to add any SysCAD key words to the Excel report. However, if no existing SysCAD report workbook has been set up, save a blank MS Excel spreadsheet, then add it to the dialog box to activate this button.
  • Generate: Generate a Trend report. This button will call the Excel automation functions to transfer trend data from SysCAD to Excel. NOTE: data is not linked between the two software packages, transfer will only happen once whenever this button is pressed.
  • Generate All: Generates ALL Trend reports in the dialog box. This button will call the Excel automation functions to transfer trend data from SysCAD to Excel. NOTE: data is not linked between the two software packages, transfer will only happen once whenever this button is pressed.

Macro Tab

Use this Tab to execute Excel Macros.

Excel Macro Tab 9.3.jpg

Columns:

  • Filename: The name of the Excel Workbook that contains the macro.
  • Name: The name of the macro. Please note that a * represents all macros in the specified Excel Workbook.
  • Folder: The path location of the specified Excel Workbook.

Buttons:

  • Add: Used to add a new macro to the list. (See heading Adding an Excel Report to SysCAD for more information.)
  • Copy: Adds another entry into the Dialog box based on an existing entry (currently selected report). Only valid if one or more entry has been added.
  • Remove: Used to remove the currently selected macro from the list.
  • Edit...: Used to change Macro name and path for the currently selected file. This does not open the file for editing purposes.
  • Edit in Excel: Opens the currently selected report in MS Excel for editing.
  • Execute: This button will call the Excel automation functions to execute the macro.

Options Tab

Use this Tab to set various SysCAD report options.

Excel Reports Options 9.3.jpg

Note: If the option Update remote and external references when opening Excel file is selected, any excel files containing links will be updated upon opening.

Right Click Pop Up Options

Right clicking on any entries on the Excel Automation: Export Reports and Import Data dialog box will bring up the following pop up list:

Excel Popup 9.3.jpg

The options on the pop up list have the same functionality as the buttons on the various tab pages.

Adding an Excel Report to SysCAD

With the Excel Automation: Export Reports and Import Data dialog box open, the user may call an existing SysCAD Excel report by adding it to the appropriate tab page. That is, a SysCAD GetTag report must be added to the Export Reports Tab and SetTag Report must be added to the Import Data tab and so on.

When the Add button is pressed, it opens the following dialog box, note this was opened from the Export Reports tab. The dialog box may be slightly different if the Add button was pressed from one of the other Tab pages.

Excel Add Report 9.3.jpg

  • The user may use the Browse button to find the MS Excel workbook containing the SysCAD report.
  • The Report name may be entered if user wants to produce only one report within the Excel Workbook.
  • Press OK to close this dialog box and return to the Excel Automation: Export Reports and Import Data dialog box.

Executing an Excel Report

There are a number of ways to execute the SysCAD reports:

  • The easiest is to press the Generate / Process button with the Excel Automation: Export Reports and Import Data dialog box open. This button will call the Excel automation functions to transfer the data between Excel and SysCAD. NOTE: data is not linked between the two software packages, transfer will only happen once whenever this button is pressed.