无法在计划任务中使用 vba 关闭 Excel App

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

提问by Adrien A.

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

我在 vba 中制作了一个代码，它适用于不同的Workbooks和Worksheets. 此代码必须在计划任务中执行。但出于一个未知的原因，我遇到了问题：

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

当我手动执行它时，它工作正常并且 excel 自行关闭。但是对于我的计划任务，Excel 将全部关闭Workbooks，Worksheets但它保持打开状态。你有我的代码：

    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.

我的 excel 保持打开状态。

Anyone can help me plz ?

任何人都可以帮助我吗？

回答by Daniel

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.

您看到的问题是因为在给定时间，一台机器上可能有多个 Excel 应用程序实例。

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.

当您手动运行该文件时，您可能会使用默认行为，即当您直接打开工作簿时，它会在您已加载的 Excel 应用程序中打开。

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.Applicationin order to handle your task. When it calls the code I quoted it ends up referencing the Excel.Applicationyou already had open (probably).

它实际上是返回当前的Excel.Application. 但是，当您通过计划任务加载它时，它会生成一个新Excel.Application任务来处理您的任务。当它调用我引用的代码时，它最终引用了Excel.Application您已经打开的（可能）。

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

由于您计划的工作簿在 的不同实例中Excel.Application运行，当您运行xlApp.Quit它时，它只会退出其他 Excel，而不是实际运行代码的 Excel。

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

如果您还想关闭自动 Excel，则需要添加Application.Quit到子文件的末尾。是的，我的意思是同时使用xlApp.QuitAND 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:

现在从技术上讲，当您加载新的 Excel 应用程序时，您可以打开多个 Excel 应用程序。如果您想关闭 Excel 的所有实例，我所知道的最简单的方法是通过 vbscript 调用终止所有名为 excel.exe 的程序的程序来终止所有这些实例。注意我没有在 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.

1. 已经打开 Excel。
2. 浏览开始菜单并打开 Excel。
3. 从 Excel 的新实例中，打开您的工作簿。

OR

或者

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

1. 已经打开 Excel。
2. 运行以下 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.

编辑 2：根据您的要求，我编写了这个简短的 vbscript 文件，它将关闭所有打开的 Excel 应用程序，而不会影响自动恢复。如果您还想避免任何，“您想保存...”警报取消注释注释部分。

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.

要运行它，只需在 Excel VBA 的末尾包含以下内容。

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

回答by Krish

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.

我可以看到您使用 Excel 应用程序打开了多个工作簿实例。您需要关闭所有工作簿实例才能获得普通的 excel 应用程序，而不是退出它。

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.

上面的代码将打开您的工作簿。在工作表上编写自定义消息保存更改并关闭。并且 xlapp 也将被终止/销毁。