Simultaneously opening a spreadsheet in Excel and Analytica

Revision as of 18:36, 28 June 2017 by Lchrisman (Talk | contribs) (Created page with "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 Worksh...")

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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 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:

SpreadsheetSetInfo( wb, 'Visible', true )

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


You are not allowed to post comments.