Excel not closing

[dstoltz]

I received this script from someone else. The script basically opens the excel document, saves it as a CSV, then closes....

It all works, except the part that closes Excel, it is still running in task manager (excel.exe*32). This script is running on a 2003 server which is logged onto and locked. After the script runs, I log on and sometimes I see Excel open at the "save as" dialog, and sometimes it's just running in task manager. Either way, the CSV file was saved correctly - I just need to kill excel.

Here's the code:

FileDir = "d:\340B-MH\"
FileName = fileitemize(Strcat(FileDir,"*.xls")) ; retrieves unsorted list
AddExtender("WWPST44I.DLL")                     ; Postie Extender for sending email information messages
AddExtender("wwctl44i.dll")                     ; Control Manager Extender - Used to close popup windows from excel

; If our file is present, then let's convert it and rename it.

   source_xls = "%FileDir%%FileName%"
   ExcelWS = ObjectOpen("Excel.Application")
   EWS = ExcelWS.Application
   EWS.visible = @true
   EWB = EWS.Workbooks
   EWB.Open (source_xls)
   xlCSV = 6 ; an Excel constant value for the file format xlCSV.    
   WBA = ExcelWS.ActiveWorkbook
   WBA.SaveAs(::FileName="d:\340B-MH\myfile.csv",FileFormat=%xlCSV%,CreateBackup=0)

; Now that we have the file in the right format, close excel. There will be a popup window, so lets close that too

   ExcelWS.quit

   If WinWaitExist("Microsoft Excel~", 5)
      TimeDelay(5)
      SendKeysTo( "Microsoft Excel~", `!n`)     ;Sends Keystrokes ALT-N to do NOT save the file
   EndIf
   timedelay(5)
   ObjectClose(ExcelWS)
 

Exit

[stanl]

My guess is you will either have to set the DisplayAlerts property of the Excel Object to @FALSE, set

WBA.Close   ;to close active workbook

[dstoltz]

Something like this?

   source_xls = "%FileDir%%FileName%"
   ExcelWS = ObjectOpen("Excel.Application")
   EWS = ExcelWS.Application
   EWS.visible = @true
   EWB = EWS.Workbooks
   EWB.Open (source_xls)
   xlCSV = 6 ; an Excel constant value for the file format xlCSV.   
   EWS.DisplayAlerts = @false
   WBA = ExcelWS.ActiveWorkbook
   WBA.SaveAs(::FileName="d:\340B-MH\afpdischarge.csv",FileFormat=%xlCSV%,CreateBackup=0)
   WBA.Close

[td]

Give it a try.  If it doesn't work, tell us which version of WinBatch you are using.  We have implemented a few changes over the last couple of years that workaround problems with some COM Automation servers not cleaning up their resources properly which in turn can cause the hanging process issue.

[dstoltz]

Using latest version - adding those lines seems to have done the trick. Thanks!