Creating an Excel Interface

Often when building simulations, we create an EXCEL interface. On the interface we include parameters we are likely to want to change, for example staff availability or cycle times and key results such as staff utilization. We build these interfaces for many reasons:

To make it easy to experiment with the simulation
To allow the client to experiment without having to learn Simul8
To document the simulation

This same interface could also be built within Simul8's internal spreadsheets, as they offer most of the capabilities that Excel does. However, connecting to EXCEL enables clients who don't use Simul8 to interact with it through EXCEL. Non-Simul8 users can populate the EXCEL file with their data and the Simul8 license holder can run their scenarios.

How to Create an Excel Interface

You can import and export simulation data to EXCEL using:

Excel Connections: You can import/export these using the Excel Connections dialog or through Excel Connection related commands in Visual Logic. Running this in Visual Logic, allows you to run them at the time point of your choice.
Visual Logic commands for transferring data.

The following examples show how the two approaches are implemented over three steps, for importing data from Excel, for using this data, and from exporting data to EXCEL.

Step 1: Getting the information from Excel

First, we want to input the staff availability pattern below from an Excel file named "Staff Availability Pattern" into Simul8. media/excel3.gif

Given that we have created an Excel Connection for it, we can use the "Import Excel Connection" command in Visual Logic for importing the data. media/importexcelconnection.png

If you 'd like to import all Connections at once, you can use the "Import all Excel Connections" command. If you haven't created an Excel Connection, or if you want to import information from EXCEL to a global variable, you can use the Get from EXCEL command. media/getfromexcel_creatinganexcelinterface.png

media/getfromexcel_creatinganexcelinterface.png

The first parameter of the command holds the Simul8 variable the information is to be written to, in this case the ss_Staff Availability. As our variable is an internal spreadsheet the cell reference [1,1] tells Simul8 what cell to start writing the information in.

The second parameter holds the names of the .xlsx file, and the sheet where the information is located. The remaining parameters tell Simul8 the EXCEL cell to start copying from and how many rows and columns of data to copy.

We try to avoid reading information in from Excel during a run (such us on Start Run), as this could really slow down the simulation. Generally, we read in all the information we need during 'On Reset' logic as this way the information will be updated just before a run.

Step 2: Using the information from Excel

Next, we want to change the staff level each hour based on the imported data, so we place our Visual Logic code in the Time Check section. Time Check logic is repeated on a fixed time interval. We look at our spreadsheet where we stored our data from Excel to set the maximum number of Resources available. We use local variables to represent the row and column numbers. We then increment these so that the next time the Visual Logic is run the next cell is read. media/creating_an_excel_interface_time_check_logic.png

media/creating_an_excel_interface_time_check_logic.png

Step 3: Sending information Back to Excel

Often you may want to report the results of your simulation back to Excel. You can do this by exporting an already created Excel Connection, using the "Export Excel Connection" Command. media/export_excel_connection.png

As when importing, you can export all Excel Connections, using the "Export all Excel Connections" command.

If you'd like to export to Excel the value of a global variable which stores the utilization result of Resource 1, you can use the Set in Excel command.

We first store the utilization result we wish to report in a global variable using the get_result_command. Please note that you have to add to KPIs, the Utilization % of Resource 1, for the command's parameter to display correctly. We then use the Set in Excel command to send the information to Excel. media/set_in_excel_creating_an_excel_interface.png

media/set_in_excel_creating_an_excel_interface.png

The first parameter of the Set in Excel command tells Simul8 what information is to be written to Excel, in this case gbl_Utilisation. The second parameter holds the names of the .xlsx EXCEL file, and the sheet where the information is located. The remaining parameters tell Simul8 where in Excel to write this information to and how many rows and columns of data to copy. (You only need the last 2 if you're copying information from a spreadsheet otherwise just enter 1 for both.)

Tips

If a lot of data has to be included in the Excel Interface, have a summary sheet that lists the main inputs and outputs clients are likely to want to examine.
If the structure of the sheets changes as the simulation evolves rather than change all your Visual Logic references you can create another sheet that is nicely formatted and has direct links to the old sheets, that you can hide.
Lock cells so people don't think they can move them around!
Save different scenarios in different Excel files
Your Excel spreadsheet must be opened to use it with the simulation. If you've forgotten to open it you'll get the error message "EXCEL is not responding to a conversation request".