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
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
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
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.
Using latest version - adding those lines seems to have done the trick. Thanks!