TL,DR: I need a way in VBA to restore how Excel 2010 behaves when opening workbooks.
I came across a situation in my macro code where I wanted to open a workbook in the background to retrieve some data from it and then close it, preferably without the end-user ever knowing I'd done so. This seemed easy enough to do: I created a new Application, set its Visible state to false, and set the Application variable I used to Nothing when I finished. Worked like a charm, the workbook was opened in the background, read, and closed without the screen even flickering to it.
But then I started to notice an unfortunate side effect. I normally have my copy of Excel 2010 set up to open all workbooks in the same instance: they occupy the same window even when they appear separately on the taskbar, and when I close an open workbook, Excel switches to a different workbook if one is available. (This is the default setting most of my clients have.) However, after running my macro, future attempts to open workbooks in Excel open in a different instance: closing the newly opened workbook reveals an Excel application with no open workbooks even though there are other workbooks open.
As the behavior of Excel is different, this is an undesirable bug.
Ideally, I'd like a means of determining how Excel should handle opening new workbooks (as in whether or not it opens the workbooks in the same instance), and resetting that default behavior after my code executes. At minimum, I'd like to be able to ensure Excel returns to opening workbooks in the same instance. Does anyone have any suggestions on how I could do so?
Bookmarks