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 opened or new workbooks may be created with the help of Visual Basic.
- Enable the Microsoft Excel Object Library in the INOSIM Basic editor (menu Tools/References):
- 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.
Set 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 Set Excel = CreateObject("Excel.Application") Set myExtWorkbook = Excel.Workbooks.Open(pathToWorkbook) Set OpenExternalWorkbook = myExtWorkbook End Function
' Don't forget the declaration of the global variable "Excel", as explained above Private Sub 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 location of your workbook myWorkbookPath = "C:\Users\INOSIMUser\Data\externalWorkbook.xlsx" Set myWorkbook = OpenExternalWorkbook(myWorkbookPath) Set myWorksheet =myWorkbook.Worksheets("Number of Batches") 'Read number of Batches myWorksheet numBatchesA = myWorksheet.Cells(1,2) '=10 numBatchesB = myWorksheet.Cells(2,2) '=15 numBatchesC = myWorksheet.Cells(3,2) '=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 above example, 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 Set myWorkbook = Excel.Workbooks.Add Set 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.