Manually Opened Excel Instance Interferes with Excel OLE Automation from VB


I have a problem with OLE Automation with Excel from VB 6.0 Pro.  I have several procedures that send data to Excel to create reports.  Some of them take a little while to create and the users work on other things while they are running.  If they go to My Computer / Explorer and double-click on an Excel file it will open in the instance of Excel VB opened and interfere with the report running.  This is how I declare my Excel related variables:

        Set oxl = CreateObject("Excel.application")
        Set oBook = oxl.Workbooks.add()
        Set oSheet = oBook.Sheets("Sheet1")

Is there any way to avoid this problem, other than tying the users' hands down while their reports are generating?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

More than likely you'll want to use early binding vs. late binding to solve some of those issues. Take a look at this thread it has some good examples and is a great source of information.

tlchavetAuthor Commented:
Sorry I should have included my dims:
    Dim oxl As Excel.Application
    Dim oBook As Excel.Workbook
    Dim oSheet As Excel.worksheet
I am using early binding.
Try using the keyword "New"

Dim oxl As New Excel.Application


Set oxl = New Excel.Application

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tlchavetAuthor Commented:

I tried that.  The user still picks up the instance opened by VB if they double-click another Excel worksheet to open.  Is there a way to have VB open it's instance "exclusively".

Well the problem is more of an Excel problem than a coding problem.

Using the following:

Dim oXL As Object
Dim oBook As Object
Dim oSheet As Object

Private Sub Command1_Click()
Set oXL = New Excel.Application
Set oBook = oXL.Workbooks.Add()
Set oSheet = oBook.Sheets("Sheet1")
oXL.Visible = True
oXL.WindowState = xlMinimized

MsgBox "Excel Open"

Set oSheet = Nothing
Set oBook = Nothing

Set oXL = Nothing

MsgBox "Excel Closed"

End Sub

This sub does create a separate instance of Excel, the problem I see is that when you open an additional workbook after your code is running Excel uses that instance to open the workbook in. However, if you perform the operation in reverse order it keeps the instances separate, a "New" instance is created for your application.

My guess would be that Excel is using the GetObject() function by default when opening a new workbook...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tlchavetAuthor Commented:
Thanks - the problem then boils down to the users needing to be patient.  Thanks a bunch for the assistance!
Sorry, I couldn't give you the answer you were looking for...

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.