CODEWITHSUNDEEP
vbaexcelscheduled-tasks
Adrien A.
Adrien A.

Reputation: 441

Can't close Excel App with vba in a scheduled task

I have made a code in vba which works with different Workbooks and Worksheets. This code must be execute in a scheduled task. But for an unknow reason I have a problem with it :

When I execute it manually, it works fine and excel closes itself. But with my scheduled task, Excel closes all Workbooks and Worksheets but it stays open. Here you have my code :

    Set xlApp = GetObject(, "excel.application")
    Set wkbMe = xlApp.ActiveWorkbook
    test = False

    xlApp.DisplayAlerts = False
    xlApp.AskToUpdateLinks = False

    'Open files
    xlApp.Workbooks.Open Filename:=MarketDataPath & WbRiskedge, ReadOnly:=True
    xlApp.Workbooks.Open Filename:=MarketDataPath & WbMarketData, ReadOnly:=True
    Set WorksheetIncoming = xlApp.Workbooks(WbMarketData).Worksheets(wsIncoming)
    Set WorksheetMarketdata = xlApp.Workbooks(WbMarketData).Worksheets(WsMarketData)

    xlApp.Workbooks.Open Filename:=GTPath & WbGoodTime, ReadOnly:=True
    Cells.Copy
    WorksheetIncoming.Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                            :=False, Transpose:=False
    Workbooks(WbGoodTime).Close
    WorksheetMarketdata.Calculate
    Worksheets(wsMarketDataForWebsite).Calculate
    Worksheets(wsMarketDataForWebsite).Activate

    If test = False Then
        Application.Run "MarketEnv.xlsm!subCreateCSV"
    End If
    Workbooks(WbMarketData).Close , False
    Workbooks(WbRiskedge).Close , False
    xlApp.DisplayAlerts = True
    xlApp.AskToUpdateLinks = True
    ThisWorkbook.Save
    ThisWorkbook.Saved = True
    xlApp.Quit
    End Sub

I have tried different solutions found on the web but nothing work. Even if I only make :

 Set xlApp = GetObject(, "excel.application")
 xlApp.Quit
 End Sub

my excel stays open.

Anyone can help me plz ?

Upvotes: 1

Views: 7190

Answers (2)

Krish
Krish

Reputation: 5917

I can see you have opened more than one workbook instances with your excel application. You need to close all workbook instances to get the plain excel application and than quit it.

try this: (pseudo code)

dim xlApp as Excel.Application
dim wBook as Excel.Workbook
dim wSheet as Excel.Worksheet

Set xlApp = new Excel.Application
Set wBook = xlApp.Workbooks.Add(wb_Path)
Set wSheet = wBook.Sheets(1)

wSheet.Range("A1").Value = "Hello this is a test from vbcode"
wbook.close saveChanges:= true
xlApp.quit

Above code will open your workbook. write a custom message on your worksheet saves the changes and closes. and the xlapp will also be terminated/destroyed.

Upvotes: 0

Daniel
Daniel

Reputation: 13142

Okay, this seems long winded... but here goes.

The problem you are seeing is because there can be more than one instance of the Excel Application on a machine at a given time.

When you are manually running the file, you are likely using the default behavior, which is that when you open a workbook directly it opens in your already loaded Excel Application.

This means that when you use:

Set xlApp = GetObject(, "excel.application")

It is actually returning the current Excel.Application. However, when you load it via the scheduled task, it generates a new Excel.Application in order to handle your task. When it calls the code I quoted it ends up referencing the Excel.Application you already had open (probably).

Since your scheduled workbook is running in a different instance of Excel.Application, when you run xlApp.Quit it only quits that other Excel and not the one actually running the code.

If you want to also close your automated Excel, you will need to add Application.Quit to the end of your sub. Yes, I do mean use both xlApp.Quit AND Application.Quit.

Now technically, you could have more than one Excel Applications open when you load the new one. If you want to close all instances of Excel, the simplest way I know would be to kill all of them via a vbscript call to a program like this which terminates all processes named excel.exe. Note I did not test this on Win 7:

Dim objWMIService, objProcess, colProcess
Dim strComputer, strProcessKill, strFilePath
strComputer = "."
strProcessKill = "'excel.exe'" 

Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")

Set colProcess = objWMIService.ExecQuery _
("Select * from Win32_Process Where Name = " & strProcessKill)
For Each objProcess In colProcess
        objProcess.Terminate
Next
WScript.Quit

Edit: I just wanted to add that you can replicate the behavior of the scheduled task manually by doing the following:

  1. Have Excel already open.
  2. Navigate through your start menu and open Excel.
  3. From the new instance of Excel, open your workbook.

OR

  1. Have Excel already open.
  2. Run the following Excel.exe [path of workbook in quotes]

Edit 2: Due to your request, I've written this short vbscript file that will close all open Excel Applications without upsetting auto-recover. If you also want to avoid any, "Do you want to save ..." alerts uncomment the commented section.

On Error Resume Next
Dim xlApp
Set xlApp = GetObject(, "Excel.Application")
Do While Err.Number <> 429
    'For each wb in xlApp.Workbooks
    '    wb.saved = true
    'next
    xlApp.Quit
    Set xlApp = Nothing
    Set xlApp = GetObject(, "Excel.Application")
Loop
Wscript.quit

To run it, just include the following at the end of your Excel VBA.

Shell "wscript.exe [path of .vbs file]"

Upvotes: 6

Related Questions