Excel automation: Why my code is hijacked?

[Jack]

Hello,
I have this routine to disconnect from Excel:

Public Sub XLDisconnectFromExcel()
If Not moExcelWS Is Nothing Then Set moExcelWS = Nothing
If Not moExcelApp Is Nothing Then Set moExcelApp = Nothing
End Sub

When the second line of that code is executed and moExcelApp is "Microsoft
Excel" then that line of code is executed properly (Excel.exe is process
terminated in Task Manager) but the code execution never returns after that
line. It just dissapears and subsequently not any other code is executed.
What can be wrong?

To connect to Excel I use this code:
Set moExcelApp = GetObject(, "Excel.Application")
If moExcelApp Is Nothing Then
Set moExcelApp = CreateObject("Excel.Application")
End If

Jack

 

[JP]

More details needed. Where are you using this code? What do you mean
"but the code execution never returns after that
line. It just dissapears and subsequently not any other code is
executed."

Returns where? Do you mean you are using calling this Sub from another
routine?


--JP

 

[Dave Peterson]

First, I don't see the purpose of checking in the first routine.

Public Sub XLDisconnectFromExcel()
Set moExcelWS = Nothing
Set moExcelApp = Nothing
End Sub

If either of those objects are already nothing, then it won't hurt.

But I don't have a guess why your program hangs up. Maybe you didn't release
all the objects??? Maybe you didn't close excel??? Maybe excel is waiting for
input from the user???

And I would expect an error if the getobject failed in the second routine.

I use this:

dim moExcelApp as Object
On Error Resume Next
Set moExcelApp = GetObject(, "Excel.Application")
If Err.Number = 429 Then
'Excel is not running
Set moExcelApp = CreateObject("Excel.Application")
err.clear
end if

 

[Jack]

Follow up.
The same happens when stepping through this code:
On Error Resume Next 'TEMPORARILY ignore errors
Set moExcelApp = GetObject(, "Excel.Application")
If moExcelApp Is Nothing Then
Set moExcelApp = CreateObject("Excel.Application") ===> after
executing that line the code will not stop at the next line (End If)
End If

Interestingly, I am using the same code in another of my app and it does
work fine on the same computer!!!!
The Reference is set exactly to the same file and the general settings are
the same:
Option Explicit
Public WithEvents moExcelApp As Excel.Application
Dim moExcelWBk As Excel.Workbook
Dim moExcelWS As Excel.Worksheet

Jack

 

[Jack]

What I mean is, that when stepping through the code in debugging mode, after
executing that line of code execution does not return to End Sub where it
should.
Jack

More details needed. Where are you using this code? What do you mean
"but the code execution never returns after that
line. It just dissapears and subsequently not any other code is
executed."

Returns where? Do you mean you are using calling this Sub from another
routine?


--JP

 

[Jack]

Problem solved.
Just removed the reference, saved the project, restarted the project and
added again the same reference.
Any thoughts on that?
Jack

 

[Dave Peterson]

I just have a question on why you're using a reference at all.

Since you're using createobject/getobject, it looks as though you could stick
with that late binding approach.

 

[Ralph]

Follow up.
The same happens when stepping through this code:
On Error Resume Next 'TEMPORARILY ignore errors
Set moExcelApp = GetObject(, "Excel.Application")
If moExcelApp Is Nothing Then
Set moExcelApp = CreateObject("Excel.Application") ===> after
executing that line the code will not stop at the next line (End If)
End If

Interestingly, I am using the same code in another of my app and it does
work fine on the same computer!!!!
The Reference is set exactly to the same file and the general settings are
the same:
Option Explicit
Public WithEvents moExcelApp As Excel.Application
Dim moExcelWBk As Excel.Workbook
Dim moExcelWS As Excel.Worksheet

Jack

Take a good look at Dave's advice.

In a nutshell: Excel is a PITA if you have more than one running.
(GetObject) Fully qualify ALL objects with the object reference. Make sure
you have cleaned up everything. Explicitly tell it whether you want to save
or not. Explicitly call .Exit. While debugging make sure DisplayAlerts is on
and when you are no longer debugging make sure they are off.

Here's is a slew of tips:
Note the suggestion to turn .DisplayAlerts back on when you Exit.
http://www.tek-tips.com/viewthread.cfm?qid=90756&page=1

hth
-ralph

 

[Ralph]

I just have a question on why you're using a reference at all.

Since you're using createobject/getobject, it looks as though you could stick
with that late binding approach.

The OP isn't using Late-binding. He is using Early-binding.
The Type (or Interface) of the object reference variable is what determines
Late or Early not the call to fetch the coClass.

eg:
Dim oApp As Object
Dim rApp As Excel.Application
Set oApp = CreateObject("Excel.Application") ' Late
Set rApp = CreateObject("Excel.Application") ' Early

-ralph

 

[Ralph]

Problem solved.
Just removed the reference, saved the project, restarted the project and
added again the same reference.
Any thoughts on that?
Jack

Whatever was hidden or being held was finally destroyed.

Unless you improve your shutdown, you will get bit again. <g>

-ralph

 

[Dave Peterson]

I should have been more explicit.

My point was since he was using this:

Set moExcelApp = CreateObject("Excel.Application")
instead of
Set moExcelApp = new Excel.application

why not just stick with late binding and drop the reference completely and
declare those variables as Objects?

 

[Steve Gerrard]

I should have been more explicit.

My point was since he was using this:

Set moExcelApp = CreateObject("Excel.Application")
instead of
Set moExcelApp = new Excel.application

why not just stick with late binding and drop the reference
completely and declare those variables as Objects?

He would lose intellisense, and have to look all the parameters up to be sure
they were correct, or wait for runtime errors. Also late binding all the calls
would slow the performance down quite a bit. The advantage of using create
object with Excel is that it is not version specific.

Back to the original post, shouldn't there be a
moExcelApp.Quit
before the
Set moExcelApp = Nothing
or is that not needed?

 

[Dave Peterson]

I develop my code with the reference. But when I release it to others, I change
it late binding. I couldn't live without the intellisense, either.

And who knows about quitting the application? I figured that there wasn't
really enough code in that post to venture a guess (no saving, no closing, no
releasing other objects???).