Monday, December 8, 2008

Closing Excel instance by VBScript

We have a test suite for Excel plug-in. Silktest built-in function does not work for Excel 2007. It is working fine for Excel 2003. Silktest is unable to close the Excel 2007 instance. We used Taskkill to kill the excel. It has created few other issues. I was looking for alternative solution. So I decided to write a vb script and got success. I have given the code below.

VBS code - To close Excel

'------------------------------------------------------------------------- ' File : CloseExcel.vbs ' Author : Palani ' Purpose : To close the excel, if it is already opened. ' '' Revision History: ''$Log: CloseExcel.vbs,v $ '' '------------------------------------------------------------------------- '' Usage '' cscript D:\rpm_scripts_palani\tools\CloseExcel.vbs '' cscript CloseExcel.vbs '******** Variables Declaration Dim gsLogFile '******** Function calls call CloseExcelApps () '-------------------------------------- ' Method : CloseExcelApps ' Author : T. Palani Selvam ' Purpose : Close Excel application. ' Parameters: - Nil ' Returns : - Nil ' Caller : - Nil ' Calls : - Nil '-------------------------------------- Sub CloseExcelApps() Dim sExcelPath 'As Variant 'Excel file '********** Excel object declaration **********' ' Excel Application object Dim objExcel 'As Excel.Application Dim objExcel2 'As Excel.Workbooks Dim objXLWorkbook 'As Excel.Workbook On Error Resume Next Set objExcel = GetObject(,"Excel.Application") If Not (IsNull(objExcel) Or IsEmpty(objExcel)) Then WScript.Echo ("Excel application instance Exists..") 'Set objXLWorkbook = objExcel.ActiveWorkbook 'You can set this property to True if you want to close a modified workbook 'without either saving it or being prompted to save it. objExcel.ActiveWorkbook.Saved = True objExcel.ActiveWorkbook.Close objExcel.Application.Quit 'objExcel.Worksbooks.Close 'objExcel.Quit Set objExcel = Nothing Set objExcel2 = GetObject(,"Excel.Application") If Not (IsNull(objExcel2) Or IsEmpty(objExcel2)) Then Set objExcel2 = Nothing WScript.Echo ("FAIL. Excel application is not closed properly.") Else WScript.Echo ("PASS. Successfully closed Excel application.") End If WScript.Echo ("End - Closing excel application instance.") else WScript.Echo ("Excel application instance does not exist!....") End If End Sub

5 comments:

Anonymous said...

I tried the vbs code, but its not closing the excel and giving message that FAIL. Excel application is not closed properly.

Steps I followed:
1. Copy the code from blog.
2. Save it as killExcel.vbs.
3. Open new excel 2007
4. Double click on vbs file.

Let me know if I am missing something here.

Palani Selvam said...

It should work fine. I have the same steps and working fine. You should check, whether you have VBScript5.7 engine and Office 2k3 SP3.

Anonymous said...

Hi Just tried the above code for closing an excel instance with multiple excel files open. I go the same message as posted above.
what would be the solution in this case.. thanks in advance..

Palani Selvam said...

Hey,

Can you add following lines instead of saving file and try it?

objExcel.Application.DisplayAlerts = False
objExcel.ActiveWorkbook.Saved = False

For multiple instances, you can put a FOR loop.

Pete Shifter said...

I've seen a lot of functions supposed to close Excel. This one is the only that do work.
Thank you very much!!!