GoldSim Blog: Automatically Export GoldSim Plots as Image Files

Posted by Jason Lillywhite

Over the last few years, some users have asked us for a feature that automatically exports images of plots in a GoldSim model. This is useful if you want to quickly update many plots used for a report but it is currently not a feature supported by GoldSim. Luckily, there is a fairly straightforward workaround that involves the help of a macro enabled Excel spreadsheet. In this post, I will walk through the steps to build this functionality using a simple example model. Using the simple example that I've created, you just run the model and images of all the plots of exported results are saved immediately.

This example does not generate images directly from GoldSim. Rather, GoldSim exports results to a spreadsheet file, which uses its own plotting capabilities to generate the plots and then uses a macro to generate the images on the fly. The steps shown in this blog post use an example GoldSim model and Excel file that you can download from our library, here.

The first step is to set up your GoldSim model to export results to a spreadsheet. If you are unsure of how to do this, please refer to our Help Documentation in the "Displaying Results in GoldSim" chapter, under the "Exporting Results" section. Below is a screen capture of my model example with the results of interest highlighted in red.

In the properties dialog of each Time History result element, I have the export set to an existing, macro-enabled excel file (note .xlsm extension). I export all results to a single worksheet called "Results From GoldSim".

This will allow GoldSim to export time history result data to Excel automatically at the end of the simulation. I've also added a Spreadsheet element to export the current date and time to a single cell in the spreadsheet. The expression "Current_Time" uses the built-in "now" function to return the current date and time on your computer and then it is exported to Excel (cell "B1" of the "Results From GoldSim" worksheet).

The purpose of exporting the current time to a cell is so that the spreadsheet can be notified that a change occurred, which will trigger a macro to run that produces the image files. The above spreadsheet element causes the current time value to be exported to cell "B1" at the end of the simulation.

The next step is to configure the spreadsheet to process the images from plots that are created from the GoldSim results. Set up the excel file with a worksheet containing all the results exported from GoldSim. I have done this with my 4 time history result elements and the current time output as shown in the screen capture below.

In the excel file, create a new tab with charts of the data. Format them as needed. You don't need to put the charts in just one sheet. They can go in as many sheets as you want but I put them in one to keep it simple. The macro used in this example will just export every chart in the entire workbook.

The final step is to create the VBA macro necessary for generating the plot images. Start up VBA by clicking on the developer tab and clicking on "Visual Basic". Now add this code to Module1, which I obtained from here:

 'small nicety to ensure two-digits for better file sorting'  
 Function NiceFileNumber(num As Integer) As String  
   If num < 10 Then  
     NiceFileNumber = "0" & num  
   Else  
     NiceFileNumber = num  
   End If  
 End Function

  
 Sub Export_Plots()  
 Dim i As Integer, exportCount As Integer  
   Dim fileNum As String, fileBase As String  
   Dim sheetObj As Worksheet  
   Dim chartObj As Chart  
   'current file location and name, with extension stripped'  
   fileBase = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, ".") - 1)  
   exportCount = 0  
   'First, export all charts that are in their own sheets'  
   For Each chartObj In ActiveWorkbook.Charts  
     fileNum = NiceFileNumber(exportCount)  
     exportCount = exportCount + 1  
     'Do the export'  
     chartObj.Export fileBase & "_chart" & fileNum & ".png"  
   Next  
   'Then, export all charts that are embedded inside normal sheets'  
   For Each sheetObj In ActiveWorkbook.Worksheets  
     For i = 1 To sheetObj.ChartObjects.Count  
       fileNum = NiceFileNumber(exportCount)  
       exportCount = exportCount + 1  
       'Do the export'  
       sheetObj.ChartObjects(i).Activate  
       ActiveChart.Export fileBase & "_chart" & fileNum & ".png"  
     Next i  
   Next  
 End Sub

Now we just need the code that triggers the above functionality. In our case, that is the change in value of cell B1 of the "Results From GoldSim" worksheet.

 Private Sub Worksheet_Change(ByVal Target As Range)  
   Dim KeyCells As Range  
   ' The variable KeyCells contains the cells that will  
   ' cause an alert when they are changed.  
   Set KeyCells = Worksheets("Results From GoldSim").[A1:B1]  
   If Not Application.Intersect(KeyCells, Range(Target.Address)) _  
       Is Nothing Then  
     ' Display a message when one of the designated cells has been  
     ' changed.  
     ' Place your code here.  
     ' MsgBox "Cell " & Target.Address & " has changed."  
     Export_Plots  
   End If  
 End Sub

I added this code to the Worksheet with the command set to Change.

In the example model file, you will see that I added an additional button that exports the images automatically. You can look at the file to see how this is done if you want. I like to include the button so I can test the functionality without having to run the GoldSim model.

Now you are ready to run the model and get the plot files automatically after the simulation is complete. Close the Excel file and just run the model. You should see the image files appear in your model directory. Here is a screen capture showing the model file directory before the run.