Working With External Excel Workbooks

Beside access to the Excel workbook built in to INOSIM (see tip Benefit From Your Excel Knowledge While Working With INOSIM), existing Excel files may be accessed or new workbooks may be created with the help of Visual Basic.

Preparation

Enable the Microsoft Excel Object Library in the INOSIM Basic editor (menu Tools/References/COM):
Create a new object variable (e.g., Excel) specified as global Public variable:

Public Excel As Object 'As public variable at top of the Macro

Assign the Excel application to that object variable by using the CreateObject function, for example in your simulation´s initialization, to make it available in all later functions.

Excel = CreateObject("Excel.Application")

With this object, you have access to MS Excel. Now you can either create new workbooks, or open existing ones.

Opening an existing external Excel workbook

To open an already existing Excel workbook, you can use the Open method.

The following Code-Snippets provide a handy function to open an Excel Workbook in one line and show how the Workbook can be manipulated from within VBA.

Private Function OpenExternalWorkbook(pathToWorkbook As String) As Workbook
	'Open external Excel Workbook
	' returns Workbook object myExtWorkbook

	Dim myExtWorkbook As Workbook

	Excel = CreateObject("Excel.Application")
	myExtWorkbook = Excel.Workbooks.Open(pathToWorkbook)

	OpenExternalWorkbook = myExtWorkbook

End Function

Public Excel as Object ' Don't forget the declaration of the global variable "Excel", as explained above

Private Sub Simulation_Init() Handles Simulation.Init
	Dim myWorkbookPath As String
	Dim myWorkbook As Workbook
	Dim myWorksheet As Worksheet

	Dim numBatchesA As Integer
	Dim numBatchesB As Integer
	Dim numBatchesC As Integer

 'Change this to the path to your workbook
	myWorkbookPath = "C:\Users\INOSIMUser\Data\externalWorkbook.xlsx"

	myWorkbook = OpenExternalWorkbook(myWorkbookPath)
	myWorksheet =myWorkbook.Worksheets("Number of Batches")

	'Read number of Batches myWorksheet
	numBatchesA = myWorksheet.Cells(1,2).value '=10
	numBatchesB = myWorksheet.Cells(2,2).value '=15
	numBatchesC = myWorksheet.Cells(3,2).value '=12

	'myWorkbook.Save 'Use to save changes to the workbook, if desired

	myWorkbook.Close 'Important!

	Console.Information("Batches of Product A: " & numBatchesA)
	Console.Information("Batches of Product B: " & numBatchesB)
	Console.Information("Batches of Product C: " & numBatchesC)

End Sub

In the example above, the Workbook is opened and values are read from the specified Worksheet.

If changes have been made in the worksheet, they can be saved using the Save method. When the workbook is no longer needed, close it with the Close method. Make sure to always close Workbooks you open, to avoid unexpected behavior and data loss.

Tip: You can also initiate manual selection of the workbook with the help of the GetFilePath function:

Creating a new Excel workbook

If you wish to create a new Excel workbook, for example, to store the results of particular simulation runs separately, you can use the Add method.

The following example code shows the steps you have to take for this purpose:

' Don't forget the declaration of the global variable "Excel", as explained above

Private Function WriteResultsToNewWorkbook()
	Dim savePath As String
	Dim myWorkbook As Workbook
	Dim myWorksheet As Worksheet

	Set Excel = CreateObject("Excel.Application")

	'Create a new workbook and
	'Add a worksheet and rename it
	myWorkbook = Excel.Workbooks.Add
	myWorksheet = myWorkbook.Worksheets.Add
	myWorksheet.Name = "Results 1"

	'Write results into the new workbook
	myWorksheet.Cells(1,1) = "Storage Product A"
	myWorksheet.Cells(1,2) = "Storage Product B"
	myWorksheet.Cells(1,3) = "Storage Product C"

	myWorksheet.Cells(2,1) = 5000
	myWorksheet.Cells(2,2) = 11250
	myWorksheet.Cells(2,3) = 4800

	'Save and close the new workbook
	savePath = "C:\Users\INOSIMUser\Data\externalWorkbookResults.xlsx"
	myWorkbook.SaveAs(savePath) 'Updates the path of the workbook, too, so it can be used with .Save now as well
	'Console.Information(myWorkbook.FullName) 'Shows the full path of the new workbook
	myWorkbook.Close

End Function

First, a new Excel workbook is created, then a new worksheet is added to it. Then, the worksheet may be edited as usual. In this example, the contents of the tanks Storage A, Storage B, and Storage C are written to worksheet “Results 1”. After all results have been written, the new workbook has to be saved. Use the SaveAs method to specify the path and file name. This updates the file path stored in the Workbook object, so that afterwards, you can use the Save method as described above. Finally, make sure to use the Close method to close the workbook.

To get more information on further helpful functions and methods, check the official Microsoft documentation for workbook and worksheet objects: https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.

Using the range object

When writing data to excel sheets it can be useful to write multiple values stored in an array variable in one command using the Range object. Unfortunately, the Range object is write-protected in the VBA .NET Version. However, it can still be used in a separate .COM macro, which is easily inserted by navigating to Insert -> Macro in the Visual Basic Editor. The demo model in .NET version that can be found in the Downloads section shows a practical example.

Downloads
(For registered INOSIM Users only)

INOSIM Project
Example Workbook
PDF printout of this Tip & Trick

More Tips & Tricks

3. September 2018

Working With External Excel Workbooks

Working With External Excel Workbooks

Preparation

Opening an existing external Excel workbook

Creating a new Excel workbook

Using the range object

More Questions?

More Tips & Tricks

Custom Curves and Properties Of Curves in INOSIM Gantt

Custom Gantt Colors

Custom Failure Handling