How to Automate the Update Function Feature in Wonderware ActiveFactory IndustrialWorkbook

Tech Note: ISS-TN075
Published Date: September 16, 2008
Primary Product: Historian Clients
Revision Date:

Applies to

  • Wonderware ActiveFactory 9.x and Wonderware Historian – All versions

Summary

It may be necessary for a developer to create a report using the ActiveFactory Workbook add-in (a part of the Wonderware ActiveFactory tool set) for users to view tag data.  These reports can grow quite large and the Refresh Sheet function does not refresh new cell population.  Because of this users would need to select and refresh every individual function in the workbook.  This document provides the procedures necessary to automate the Refresh Function on every function in the Wonderware ActiveFactory IndustrialWorkbook.

Procedure

1. First open Wonderware ActiveFactory IndustrialWorkbook from Start->All Programs->Wonderware->ActiveFactory->Workbook.

2. Next install the reference to Wonderware ActiveFactory Workbook, so that you will have an access to Wonderware ActiveFactory specific functions.  Start by selecting Tools->Macro->Visual Basic Editor.

3. When the Visual Basic Editor opens, select Tools->References.

4. The References VBAProject window will open. Scroll down through the list until you find ActiveFactoryWorkbook and check the box.  When finished press the OKbutton and close Microsoft Visual Basic – ActiveFactoryWorkbook window.

5. Next create your new report using IndustrialWorkbook Excel. Here you can automate the workbook by providing dynamic start and end times or even a hybrid of each. For example:
Start Date: =now()-24:00:00
End Date:  =now()

Note: Take note of where the first cell containing the function is located. For instance, in the example below there are two starting positions, one at cell R6C1 (A6) and one at R6C4 (D6).

6. Now that the report has been created and we have taken note of which cell the function begins, we will proceed by selecting Tools->Macro->Macros.

7. The Macro window will appear.  To create a new macro you will first need to enter a name for the macro (for the purpose of this document I chose to name the macro Button_Refresh). Once you have entered a name click Create.

Note: To have the macro run as soon as the report is opened name the macro Auto_Open (without quotes). You could also create two macros here, which would be:
1) Auto_Open to have the macro run as soon as the report is opened, and
2) Button_Refresh so the macro can be utilized at any point in time.

8. Within a new module you will now write the macro. When finished in the editor, close the “Microsoft Visual Basic” window.

A copy of the macro used in this Tech Note has been written below:

Sub Button_Refresh()
Worksheets(“Sheet1″).Activate
Application.Goto Reference:=”{function starting position}”
mnuRefreshSelection
Application.Goto Reference:=”{function starting position}”
mnuRefreshSelection
End Sub

9. Now that the macro has been written we will import a picture of a button that the users can click on to run the script.  To do this first create an image of a button and save it somewhere on your system.  Then select Insert->Picture->From File.

10. Locate your button and click Insert.

11. Now that the button has been imported we need to assign the newly created macro to the button.  To do this right-click on the button and select Assign Macro

12. Select your newly created macro and click OK. You now have a button in the Wonderware ActiveFactory IndustrialWorkbook that users can click on to refresh all of the functions you created in the report.

Wonderware ActiveFactory IndustrialWorkgroup Methods

The following methods execute Workbook menu commands

Method Used to
mnuAbout Open the About dialog box
mnuAddDSN Open the Server List Configuration dialog box
mnuAggregates Open the Aggregate Values wizard
mnuAlarm Open the Alarm Values wizard
mnuAnalysis Open the Tag Analysis wizard
mnuBaseDate Open the Set Base Date/Time dialog box
mnuConvert Convert the function in the selected cell to values
mnuConvertSheet Convert the functions in the active sheet to values
mnuEditFunction Open the appropriate wizard for the selected function
mnuHelp Open the Help file
mnuHistory Open the History Values wizard
mnuInSQL Open the Server Details dialog box
mnuLive mnuLive Open the Live Values wizard
mnuOptions Open the Options dialog box
mnuQuery Open the Direct Query dialog box
mnuRefreshSelection Refresh the selected function
mnuRefreshSheet Refresh the active worksheet
mnuSnapSearch Open the Event Snapshot Tag Selection dialog box
mnuSnapShot Open the Event Snapshot Values wizard
mnuSumTagSearch Open the Summary Tag Selection dialog box
mnuSumTagValues Open the Summary Values wizard
mnuTagDesc Open the Tag Details wizard
mnuTagSearch Open the Tag Selection dialog box

All Industrial Software Solutions Tech Notes are provided "as is" without warranty of any kind.