[RESOLVED] Object Varialbe or With Block Not Set

[dw85745]

Is there a solution to this when executing a program from VB (Excel for example) then closing Excel directly INSTEAD of closing the VB App which will close Excel.

For example:

1) Excel started using late binding

   'Test the attachment of any possible already running instances our our desired Office app
   'If unsecussful then it will direct the code to execute the error handler - "MyError"
   Set oxlApp = GetObject(, "Excel.Application")
  
   'Test if it failed
   If TypeName(oxlApp) = "Nothing" Then
               
      'If it gets to here then we have been returned from the error handler
      'So we will want to create a new instance of the application object
      Set oxlApp = CreateObject("Excel.Application")
      
   End If

2) User closes Excel using "X" on Excel Form
3) User then closes the VB App which executes the following code:

         oxlApp.ActiveWorkbook.Close False

4) Error: "Object Varialbe or With Block Not Set" is then raised because object is no longer valid.

5) NOTE: For whatever reason oxlApp.Quit can be executed without raising the above error.

[RhinoBull]

It depends on the scope of the variable - it was defined in local procedure then of course it won't valid outside of it; declare it in general section or in the module - whatever works in your situation.
If there is additional that rests the object to nothing then it will be invalid as well...

Examine your code.

[techgnome]

the reason one fails and the other doesn't is because ActiveWorkbook is no longer valid... but the App is still running... check your processes... so when you then run the oxlApp.Quit, it closes Excel and ends gracefully.

-tg

[Bonnie West]

If Not oxlApp.ActiveWorkbook Is Nothing Then oxlApp.ActiveWorkbook.Close False

[techgnome]

you need to check the ActiveWorkbook... not just the Application object... you can have a valid Application object but an invalid ActiveWorkbook reference...
if oxlApp really was invalid, then the .Quit command would also fail... but since it isn't, that tells me that it's the ActiveWorkbook reference that is failing... which makes sense... if the user "closes" Excel, there is no longer an ActiveWorkbook.

-tg

[dw85745]

Thanks for responses to date:

techgnome:

if the user "closes" Excel, there is no longer an ActiveWorkbook.

As you aptly point out it appears when user is closing Excel directly, the ActiveWorkbook is closed (by Excel I assume) and consequently the reference (in VB) is no longer valid (left hanging).
Excel is still running in the background (I assume because of COM), hence the oxlApp VB object reference is still active.
Using "On Error Resume Next" allows the code to bypass the ActiveWorkbook reference and excecute oxlApp.Quit, which shuts down Excel from the VB App
and does not generate the Error (call to my routine).

Other than "ON ERROR Resume Next" any other work around ?

///////////// UPDATE //////////////////////

Modifying Bonnie West's post (Thanks Bonnie), the following code worked

         If Not oxlApp.ActiveWorkbook Is Nothing Then oxlApp.ActiveWorkbook.Close False

[techgnome]

Thanks for responses to date:

techgnome:



As you aptly point out it appears when user is closing Excel directly, the ActiveWorkbook is closed (by Excel I assume) and consequently the reference (in VB) is no longer valid (left hanging).
Excel is still running in the background (I assume because of COM), hence the oxlApp VB object reference is still active.

BINGO! Now you've got it...

Using "On Error Resume Next" allows the code to bypass the ActiveWorkbook reference and excecute oxlApp.Quit, which shuts down Excel from the VB App
and does not generate the Error (call to my routine).

GAH! Noooo! *Imagine someone running through the forums in slow-motion, tripping over threads*

Other than "ON ERROR Resume Next" any other work around ?

///////////// UPDATE //////////////////////

Modifying Bonnie West's post (Thanks Bonnie), the following code worked

         If Not oxlApp.ActiveWorkbook Is Nothing Then oxlApp.ActiveWorkbook.Close False

YESSSSS! That's the appropriate code there...

-tg

[si_the_geek]

While that is the right way to do it, using ActiveWorkbook is a dodgy thing to be doing.... because it can be (and given what you've posted, is likely to be) an entirely different workbook to what you are expecting.

If it is a different workbook, you've just destroyed whatever changes the user (or another program) made to it since it was last saved. Given that some users work for hours without saving, that's a nasty risk to take.

What I would recommend is using a variable to represent the actual workbook you want to work with (which may be one your program opens, or originally taken from ActiveWorkbook when it is expected). There are examples of how to do that in my Excel automation tutorial (link in my signature).

[dw85745]

Forgot to thank techgnome. Thank you techgnome.

si_the-geek: Code was just a quick example. Give user ALL options to save workbook. Thanks for the input and heads-up.