vba 从 .vbs 文件中杀死 Excel.exe

kill the Excel.exe from the .vbs file

提问by arun_roy

Suppose I am running an .vbs script to work with Excel sheet,now if somehow due to any reason any error occurs,I want to quit that .vbs script to run and besides to kill the process EXCEL.EXE from that .vbs file immedaitely.

假设我正在运行一个 .vbs 脚本来处理 Excel 工作表，现在如果由于某种原因发生任何错误，我想退出该 .vbs 脚本以运行，并且除了立即从该 .vbs 文件中终止进程 EXCEL.EXE .

After killing that process EXCEL.EXE, i want all the data change has been made by the script on the excel sheet should need to be ROLLBACK

杀死该进程EXCEL.EXE后，我希望excel表上的脚本所做的所有数据更改都需要回滚

Is it possible?

是否可以？

UPDATE:

更新：

I have a code with me, but how should I embed it with my script,I couldn't understand:

我有一个代码，但是我应该如何将它嵌入到我的脚本中，我无法理解：

 Option Explicit
 Dim objService,Process
 set objService = getobject("winmgmts:")

 for each Process in objService.InstancesOf("Win32_process")
  WScript.echo Process.Name & vbTab & Process.processid
 Next

SCRIPT

脚本

  Option Explicit



  Set objExcel1 = CreateObject("Excel.Application")'Object for Condition Dump

  strPathExcel1 = "D:\VB\Copy of Original Scripts\CopyofGEWingtoWing_latest_dump_21112012.xls"
  objExcel1.Workbooks.open strPathExcel1
  Set objSheet1 = objExcel1.ActiveWorkbook.Worksheets(1)
  Set objSheet2 = objExcel1.ActiveWorkbook.Worksheets("Bad Data")

 objExcel1.ScreenUpdating = False
 objExcel1.Calculation = -4135  'xlCalculationManual

 IntRow2=2
 IntRow1=4
  Do Until IntRow1 > objSheet1.UsedRange.Rows.Count

  ColStart = objExcel1.Application.WorksheetFunction.Match("Parent Business Process ID", objSheet1.Rows(3), 0) + 1 

Do Until ColStart > objSheet1.UsedRange.Columns.Count And objSheet1.Cells(IntRow1,ColStart) = ""

    If objSheet1.Cells(IntRow1,ColStart + 1) > objSheet1.Cells(IntRow1,ColStart + 5) And objSheet1.Cells(IntRow1,ColStart + 5) <> "" Then

    objSheet1.Range(objSheet1.Cells(IntRow1,1),objSheet1.Cells(IntRow1,objSheet1.UsedRange.Columns.Count)).Copy
    objSheet2.Range(objSheet2.Cells(IntRow2,1),objSheet2.Cells(IntRow2,objSheet1.UsedRange.Columns.Count)).PasteSpecial
    IntRow2=IntRow2+1
    Exit Do

    End If

ColStart=ColStart+4
Loop

IntRow1=IntRow1+1
Loop

objExcel1.ScreenUpdating = True
objExcel1.Calculation = -4105   'xlCalculationAutomatic

'=======================
objExcel1.ActiveWorkbook.SaveAs strPathExcel1
objExcel1.Workbooks.close
objExcel1.Application.Quit

'======================

'======================

TerminateCode

终止代码

   strComputer = "FullComputerName" 
   strDomain = "DOMAIN" 
   strUser = InputBox("Enter user name") 
   strPassword = InputBox("Enter password") 
   Set objSWbemLocator = CreateObject("WbemScripting.SWbemLocator") 
   Set objWMIService = objSWbemLocator.ConnectServer(strComputer, _ 
   "root\CIMV2", _ 
   strUser, _ 
   strPassword, _ 
   "MS_409", _ 
   "ntlmdomain:" + strDomain) 
   Set colProcessList = objWMIService.ExecQuery _
   ("SELECT * FROM Win32_Process WHERE Name = 'notepad.exe'")
 For Each objProcess in colProcessList
  objProcess.Terminate()
 Next

thanks,

谢谢，

回答by Ekkehard.Horner

The comments show that it's a bad idea to use WMI to kill the zombie Excel.exe left over from a aborted script. The correct way is to tame VBScript's horrible error handling by putting a OERN..OEG0 around a function that contains your former top-level code:

评论表明，使用 WMI 杀死中止脚本遗留的僵尸 Excel.exe 是一个坏主意。正确的方法是通过在包含您以前的顶级代码的函数周围放置 OERN..OEG0 来驯服 VBScript 可怕的错误处理：

Option Explicit

Dim goFS    : Set goFS    = CreateObject("Scripting.FileSystemObject")
Dim goWAN   : Set goWAN   = WScript.Arguments.Named
Dim goExcel : Set goExcel = Nothing
Dim goWBook : Set goWBook = Nothing
Dim gnRet   : gnRet       = 1
Dim gaErr   : gaErr       = Array(0, "", "")

On Error Resume Next
gnRet = Main()
gaErr = Array(Err.Number, Err.Source, Err.Description)
On Error GoTo 0
If 0 = gaErr(0) Then
   If goWBook Is Nothing Then
      WScript.Echo "surprise: no oWBook, won't save"
   Else
      WScript.Echo "will save"
      goWBook.Save
   End If
Else
   WScript.Echo Join(gaErr, " - ")
   If goWBook Is Nothing Then
      WScript.Echo "surprise: no oWBook, won't close"
   Else
      WScript.Echo "will close False"
      goWBook.Close False
   End If
End If
If goExcel Is Nothing Then
   WScript.Echo "surprise: no oExcel, won't quit"
else
   WScript.Echo "will quit"
   goExcel.Quit
End If
WScript.Quit gnRet

Function Main()
  Main = 1
  If goWAN.Exists("alpha") Then Err.Raise vbObjectError + 2, "Main()", "before CO(Excel)"
  Set goExcel = CreateObject("Excel.Application")
  Set goWBook = goExcel.Workbooks.Open(goFS.GetAbsolutePathName("..\data\savexcel.xls"))
  Dim oRngA1 : Set oRngA1 = goWBook.Worksheets(1).Range("$A1")
  oRngA1.Value = oRngA1.Value + 1
  Dim oRngA2 : Set oRngA2 = goWBook.Worksheets(1).Range("$A2")
  If goWAN.Exists("beta") Then
     oRngA2.Value = oRngA1.Value / 0
  Else
     oRngA2.Value = oRngA1.Value / 2
  End If
  Main = 0
End Function ' Main  

output of three runs:

三个运行的输出：

cscript savexcel.vbs
will save
will quit

cscript savexcel.vbs /alpha
-2147221502 - Main() - before CO(Excel)
surprise: no oWBook, won't close
surprise: no oExcel, won't quit

cscript savexcel.vbs /beta
11 - Microsoft VBScript runtime error - Division by zero
will close False
will quit

If you open the task manager and check the sheet from time to time with Excel, you'll see that

如果您打开任务管理器并使用 Excel 不时检查工作表，您会看到

1. there won't be Excel.exe zombies (execpt if you use a debugger)
2. the .xls won't be changed in case of errors

1. 不会有 Excel.exe 僵尸（如果您使用调试器，则执行）
2. 如果出现错误，.xls 不会更改

See herefor a little bit of background wrt the code structure/layout/Main() function.

有关代码结构/布局/Main() 函数的一些背景信息，请参见此处。

UPDATE - to explain my down vote of @oracle's answer:

更新 - 解释我对@oracle 答案的反对票：

option explicit
--  A must
on error resume next
--  Global OERN - a short way to desaster

dim xl: set xl = CreateObject("Excel.Application")
--  Unchecked - script will continue, even if "ActiveX component can't create object"

dim book: set book = xl.WorkBooks.Open("...")
--  Unchecked - script will continue, even if "File not found"

' manipulate book etc
' whenever there is a chance of error, or at the end of the script
' check the err object and clean up if necessary

book.worksheets(1).range("whatever") = interesting computation which fails
if err.Number <> 0 then
    SbCleanUp xl
    WScript.echo err.Description
    err.clear
--  SbCleanUp has quit Excel!
end if

-- now repeat those lines for each action you think of as risky
-- (and forget it for all those actions which really are dangerous)

' update changes, close Excel
book.Save
--  How? SbCleanUp has quit Excel!
--  What about error checking? Forgot it? Can't be bothered?

SbCleanUp xl
--  Why? SbCleanUp has quit Excel!

--  As we have no indication of trouble, let's
--  save the data based on wrong computations
--  in a .txt file and delete the .xls we don't
--  need anymore.
--- **OOOPS**

Sub SbCleanUp(byref xl)
    if not (xl Is Nothing) then
        dim book
        for each book in xl.WorkBooks
            book.Saved = true
        next 'book
        xl.Quit
    end if
End Sub

Update II:

更新二：

I revoked the down vote on @oracle's answer, because the WScript.Quit in the error handling If block will stop the script in case of thiserror. But consider: All otherunchecked errors will stay hidden and won't abort the program, and each check will cost you about 5 lines of boilerplate code.

我撤销了对@oracle 答案的反对票，因为错误处理中的 WScript.Quit If 块将在出现此错误时停止脚本。但请考虑：所有其他未经检查的错误将保持隐藏状态，不会中止程序，每次检查将花费您大约 5 行样板代码。

回答by Ekkehard.Horner

Basically, how I work with Excel is to call xl.Quit()in an error handler. To make sure that no changes are saved to the book, use book.Saved = truebeforehand. This prevents Excel complaining about opened workbooks that have been changed

基本上，我使用 Excel 的方式是调用xl.Quit()错误处理程序。要确保没有更改保存到书中，请book.Saved = true事先使用。这可以防止 Excel 抱怨打开的工作簿已更改

option explicit
on error resume next

dim xl: set xl = CreateObject("Excel.Application") 

dim book: set book = xl.WorkBooks.Open("...")
' manipulate book etc
' whenever there is a chance of error, or at the end of the script
' check the err object and clean up if necessary
if err.Number <> 0 then
    SbCleanUp xl
    WScript.echo err.Description
    err.clear
    WScript.Quit 1
end if

' update changes, close Excel
book.Save
SbCleanUp xl

Sub SbCleanUp(byref xl)
    if not (xl Is Nothing) then
        dim book
        for each book in xl.WorkBooks
            book.Saved = true
        next 'book
        xl.Quit
    end if
End Sub