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.
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
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,
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:
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
See here for a little bit of background wrt the code structure/layout/Main() function.
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
The WScript.Quit in the error handling If block will stop the script in case of this error. But consider: All other unchecked errors will stay hidden and won't abort the program, and each check will cost you about 5 lines of boilerplate code.