Excel application object in VB (apparently) prevents normal use of Excel while VB program is running

Question

I have made a Visual Basic program in Visual Studio (NOT VBA) that creates an instance of Excel and uses it throughout the program to open existing/create Workbooks. The problem I'm encountering is that any time after the instance of Excel is created, I am unable to completely open a workbook normally (from within windows explorer). I say 'completely' because Excel DOES appear open, but the menus and workbook itself don't actually populate. The Excel border just sits there and the busy cursor is shown when I hover over it.

Once the VB program is closed and that object is released, Excel returns to 'normal' functionality. Additionally, when I quit the VB program, the partially opened workbook also goes closes, almost as though it was attached to the instance of Excel I created in code.

The instance of excel created should be totally silent and the user should never even know it's being used. To that end, I'd like the user to be able to open other workbooks as though Excel isn't already being used elsewhere. This is important because other Excel workbooks might need to be opened for reference by the user during runtime.

I declare the object this way because there are many sub routines in various modules that all might want to use the instance of Excel.

Public Shared XLapp As New Excel.Application

The following code runs when the first form opens in the applicaion. Once this bit runs and the XLapp object gets set to a new Excel.Application, I lose the ability to open a workbook normally, as described above.

If IsNothing(XLapp) Then XLapp = New Excel.Application
XLapp.Visible = False
XLapp.DisplayAlerts = False
XLapp.EnableEvents = False
XLapp.ScreenUpdating = False

After creating a workbook object, I'll later in the code, and in various places throughout, open a workbook typically as below:

Dim OpenedWorkbook as Excel.Workbook = Nothing
[...]
OpenedWorkbook = XLapp.Workbooks.Open(workbook_filepath)

I've always been under the impression that if I'm opening workbooks using this particular object (XLapp) that when a workbook is opened in windows explorer that a new instance of Excel would be created. That is to say, the instance of Excel my program creates should be isolated from other instances of Excel, but that doesn't seem to be the case here.

Is there something else I need to do (or have I done something wrong?) to allow the user to use Excel normally while my program is running?

Thanks in advance for the help and for having patience with this VB newbie!

Take a look at superuser.com/questions/431061/… - may help? — aucuparia

Heinzi Heinzi · Accepted Answer · 2015-07-01T13:51:37

(Sorry, too long for a comment.)

You might be using the wrong tool for the job. You write:

The instance of excel created should be totally silent and the user should never even know it's being used. To that end, I'd like the user to be able to open other workbooks as though Excel isn't already being used elsewhere.

The purpose of Excel interop is to remote-control Excel, just like a user would interact with it. However, users don't interact with silent, non-visible Excel windows. It's just not what Excel interop is made for.

I suspect that you don't really want to remote-control a locally installed Excel instance. What you really want to do is to open and manipulate Excel workbooks. Then do just that: Use one of the Excel libraries for .NET and modify the Excel files directly (personally, I like SpreadsheetLight, but others are fine as well). Additional bonus: Your users don't need to have Excel installed.

Excel application object in VB (apparently) prevents normal use of Excel while VB program is running

2 Answers