Tips & Tricks - Use VMGSim's COM Engine to Populate Custom Datasheets

This example showcases the use of VMGSim’s COM engine to populate custom datasheets. VMGSim’s COM Engine an be used to develop custom applications such as tailored interfaces to drive your simulation, or to create custom reports. Other examples are already available inside the documentation folder. These examples cover different uses of the COM engine, such as, Case Study, Optimization, and Multiple case material stream summaries.

Although, VMGSim’s reporting system already includes built-in datasheets and allows users to import customized datasheets. For some situations you may want to have a more tailored workflow. Populating custom datasheets using our COM engine opens possibilities such as the design of case studies that create datasheets of multiple scenarios in the same run.

In this example a “Design” and an “Output” sheets are used to create the datasheets. In the Design sheet, the paths that map to variables are specified; for example, in cell A1 you can type “DeltaP”. In the OutPut sheet, the value of each cell will be written when a valid variable is found; for instance, when it finds “unitOp.DeltaP”, it will go to A1 and write the value of the variable.

The existing Case Study.xls example will be used as the starting point. The Case Study example already contains the necessary methods to start the COM Engine and to open a VMGSim case. Most of the changes to the Case Study Example will be implemented in the method mainModule.RunSimulation.

Now, let’s start the example.

  • Open the automation example Case Study.xls. (Documentation\VMG Automation\Excel Examples\Case Study).
  • Add two sheets to Case Study.xls. Name one “Design” and the other ” OutPut”. Save the file as “DataSheet Automation”.
  • Open VMGSim’s manual example VMGSimRatingCase1.vmp, and export to excel the first page of the API660 SI Report of Hx1.


  • Copy Sheet1 from the exported file. Paste it in cell A1 of sheets “Design” and “OutPut”.


  • The “Design” sheet needs to be filled with the information that the COM Engine will use to populate the “OutPut” sheet. Cells where values are required to be read from VMGSim need to contain a string that maps to a variable. For example, cell E11 should map to the variable “/Hx1.InShell.Fluid.Vap.MassFlow” . Note that to make the datasheets reusable for other cases, use relative paths to the unit operation. This means that the part “/HX1.” should be removed, and only the remaining “InShell.Fluid.Vap.MassFlow” will be kept.
  • Clear all the values from the sheet “OutPut”. 
  • Enable the Copy Path feature, this feature will make the job of filling up the “Design” sheet easier. This setting adds the Copy Path entry to the right click menu in VMGSim. The setting is located in the Options form (Tools -> Options) under Configuration/General.


  • Now, here comes the not so exciting part. Copy all the paths from the live report in VMGSim using copy path and place the relative paths in the corresponding cells. In this example, the information is being copied from a datasheet, so data is already in the order that is needed. In cases where there is no template to extract the information from, the “Copy Path” feature can be used from almost all the grids that are found within VMGSim, these include: Material Ports, Equilibrium Results, Profiles Tabs, Summary and Settings sections. In the figure below, note that the “Design” sheet contains the paths of the variables and the “OutPut” does not have values yet.


  • Now, it is time to redesign the Main sheet to make it useful for datasheet populating. Change the title, remove rows below row 17, add a section to type the unit operation path and; update the instructions and the screen capture.


  • With the report defined and the Main page redesigned it is time to update the VBA code to implement the algorithm to populate the sheet “OutPut”. The modified methods can be viewed in the file DataSheet Automation.xls

The figure below shows the heart of the algorithm.


The algorithm is basically going through all the cells in the “Design” sheet, and every time it finds a valid variable it goes and writes the value in the same cell of the “OutPut” sheet. In the example, vVar is declared as a VMGVariable and vUnitOp is declared as a VMGUnitOperation.

Luis Duhne, VMG Calgary

Please contact your local VMG office for more information.

To Top