View source for Simultaneously opening a spreadsheet in Excel and Analytica

It can be useful to have the same spreadsheet open in Excel at the same time you are reading from it or writing to it from Analytica using the [[Functions To Read Excel Worksheets|Spreadsheet functions]]. But when you attempt to open it simultaneously, what happens is confusing and seems at first inconsistent. This page demystifies what is going on and discusses how to get them open at the same time.

== Open in Excel first ==

The easiest and least confusing way to view a spreadsheet in Excel at the same time that your model is reading from or writing to it is to open the model in Excel ''before'' you evaluate [[SpreadsheetOpen]] from your model. When the model is already open in an Excel instance, [[SpreadsheetOpen]] connects to the already-open instance, and the data you see in Excel will be exactly the data that you model reads, and any data written by your model will be immediately visible in the Excel UI. This is especially nice when debugging.

== [[SpreadsheetOpen]] first, but with Excel running ==

If the Excel application (with its User interface) is already running and visible when [[SpreadsheetOpen]] is evaluated, but the spreadsheet your model opens isn't already open in Excel, then [[SpreadsheetOpen]] will use the existing Excel.exe process to open the spreadsheet, but the spreadsheet itself will not immediately show up in the Excel application. The spreadsheet in this case is in a hidden state. To view the spreadsheet, go to the '''View''' tab in Excel and select '''Unhide''' in the '''Window''' section of the toobar ribbon. Once unhidden, you'll be viewing the same instance of the spreadsheet in the Excel UI as is used by your model.

The key concept to understand for this case is the fact that there is only ONE Excel.exe process running. You can verify this by looking in Task Manager. Using '''Unhide''' only works when the Excel UI and the Excel instance used by [[SpreadsheetOpen]] are the same process instance.

== [[SpreadsheetOpen]] before Excel is running ==

This is the case that causes the most confusion. Your model opens the spreadsheet before the Excel application is launched. [[SpreadsheetOpen]] launches an Excel.exe process instance, and loads the model into that instance. The problem is that when you then launch the Excel application from Windows, it launches the Excel UI in a second Excel.exe process. If you attempt to open the model in that UI, it will inform you that the spreadsheet is already in use, and you'll only be able to open a separate copy, usually in read-only mode. This is usually not useful. Since it isn't the same instance of the spreadsheet, you cannot immediately view changes your model makes to the spreadsheet, etc.

The problem in this case occurs because there are two separate instances of Excel.exe running -- one in used by [[SpreadsheetOpen]], and one in use by the Excel UI. You can verify this by looking at Task Manager's process view, where you'll see two Excel.exe process instances running. In order to view the same spreadsheet instance simultaneously, you need both to be using the same, single Excel.exe instance.

To my knowledge, it is not possible to start the Excel application UI from the Windows menus in such a way that it uses the currently running Excel.exe process instance. But it is possible to start it from Analytica as follows. From a button, or from the typescript window, evaluate:

::<code>[[SpreadsheetSetInfo]]( wb, 'Visible', true )</code>

The Excel UI appears (using the same process in use by Analytica). Then select the '''View''' tab in Excel and '''Unhide''' on the toolbar ribbon, and select your spreadsheet. 

== See Also ==

* [[Functions To Read Excel Worksheets]]
* [[Functions to Write Data to Excel Worksheets]]