upgraded to excel 2007 - application.quit doesn't work w/ms

[kevin]

hello, we're in the process of upgrading to office 2007 at my work. i'm having an issue with excel, unfortunately...i've been searching for a while and still haven't found answer, hope someone can help!

in my excel file, i have a function that is called automatically when it is opened. everything works fine except the last line...

excel code

Code: Select all

Application.Quit

old ms (for excel 2003)

Code: Select all

Run Program>C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE "q:\excelfile.xls"

new ms (for excel 2007)

Code: Select all

Run Program>C:\Program Files\Microsoft Office\OFFICE12\EXCEL.EXE "q:\excelfile.xls"

no problems in excel 2003...the application closes. however, using excel 2007 "Application.Quit" will not do anything if called from macro scheduler (v10.1.20). the weird thing is if i open up the file from windows explorer "Application.Quit" works fine, and excel 2007 closes as it should. any thoughts? thanks!

[Marcus Tettmar]

I cannot replicate.

I created a simple macro enabled workbook with the following auto open function:

Code: Select all

Sub WorkBook_Open()
    MsgBox "Hello"
    Application.Quit
End Sub

I then tried both of the following:

Code: Select all

ExecuteFile>"C:\Users\Public\Documents\Book1_Quit.xlsm"

RunProgram>"C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE" "C:\Users\Public\Documents\Book1_Quit.xlsm"

And both forms worked fine - the book opened, displayed the message box and then quit.

This is using Excel 2007.

[kevin]

is it possible this is because the original file was created in Excel 2003? i will do some testing and reply back, thanks

[kevin]

okay, my problem doesn't appear to be related to which version of Excel created the file. i replicated the file except in 2007, and i get the same results.

using macro scheduler, if i open it using excel 2003 it opens and closes fine. if i open from windows explorer it also opens and closes fine. however, opening using excel 2007 fails to close. here is my code:

Excel wb open

Code: Select all

Call m_AddAddin.AddAddin

Excel m_AddAddin module

Code: Select all

Option Explicit

Public Sub AddAddin()

  Dim oAddIn As AddIn

  MsgBox "At least something ran."

  ' Set object to Addin
  Set oAddIn = AddIns("MyVendor Addin")
  
  ' Install if it's not installed
  If Not oAddIn.Installed Then
    oAddIn.Installed = True
  End If
  
  Set oAddIn = Nothing
  
  MsgBox "Trying to stop alerts."
  
  ' Close current workbook, Quit application
  Application.DisplayAlerts = False
  
  MsgBox "Trying to close now!!!"
  Application.Quit
  
  MsgBox "Ended???"
End Sub

i use an 'RP_WAIT=1' before 'Run Program' but changing that to '0' didn't seem to make any difference. maybe it's a settings thing...

[Marcus Tettmar]

Again, your code worked fine for me. Except that I had to comment out the add in parts, as I don't have that add in. Perhaps it is related to the addin. Did you try my simple code:

Sub WorkBook_Open()
MsgBox "Hello"
Application.Quit
End Sub

If you try a new XL 2007 workbook with that code in it, does it work?

[kevin]

i tried your suggestion w/only those two lines, and i still get the same results unfortunately. fine in windows explorer, fine using ms calling excel 2003, does not quit using ms calling excel 2007.

i tried checking "trust access to vba object model" and a couple other things in excel but nothing seems to have worked. okay well thanks for your help; i'll hack away at it again tomorrow (i'm in asia).

[Marcus Tettmar]

Make sure you're saving the file as an XLSM file (With Macros) as Excel 2007 xls files do not allow macros. Wonder if that's the issue?

[kevin]

nah i did make sure to save as .xlsm (the messagebox worked fine). if it works fine for you and not for me maybe you're right about the add-in. or settings issue. anyway sounds like something i need to figure out on my side unless any future releases after v10.1.20 could've addressed something like this?

[Marcus Tettmar]

No, this has to be an Excel issue. And just to be sure I just ran the same test in v10.1.21 and it worked fine.