Executing windows shell command involving a VB script via SQL Server Job Agent or Windows Scheduler

Question

I am having a problem with execution of a shell command involving a VB script via a SQL Server Agent and also Windows Scheduler (I am trying to get at least one of these options to work).

The command runs and completes fine when executed manually from the Windows command prompt. Below is the actual command (It consists of a VB script with two parameters being passed):

XlsxToCsv.vbs DataDictionaries.xlsx DataDictionaries.csv

XlsxToCsv.vbs is a VB script that edits and converts the xlsx file to csv format.

When I schedule the above command to run via the SQL Server Job Agent, it starts running the VB script, completes its first step, but then sort of never ends or does not continue. The file that it is supposed to create, does not get created. So the job step just keeps running and never ends.

Here is what is inside the VB Script:

'1. Delete the CSV file.
dFile = WScript.Arguments.Item(1)
SET oFso = CREATEOBJECT("Scripting.FileSystemObject")
oFso.DeleteFile dFile

'2. Read the input Excel file, replace , with c1o2m3m4a5 and save the file as csv, using the second parameter as the csv file name.
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
Dim oSheet
Set oSheet = oBook.Worksheets(1)
Dim oRange
Set oRange = oSheet.UsedRange
oRange.Replace ",", "c1o2m3m4a5"
oBook.SaveAs WScript.Arguments.Item(1), 6
oBook.Close False
oExcel.Quit

It seems that when executed by the SQL Server Agent, the above VB script gets "paused" somewhere and is waiting on something. However I am puzzled, because it runs just fine manually from MS-DOS prompt (or command prompt, did not try it via the power-shell though).

Can anyone spot a problem with the VB script or perhaps there is something additional needed to execute the above command properly via the SQL Server Agent?

Please help :)

Answer 1

1. Script did not continue due to alerts being displayed during the execution. Following command turns the alerts off: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-displayalerts-property-excel

oExcel.DisplayAlerts = False

2. I also needed to make sure that the SQL Server agent user had full permissions to the special directories discussed in that link. http://justgeeks.blogspot.com/2012/10/troubleshooting-microsoft-excel-cannot.html

Final script looks like this and get executed without issues:

'Define an object to reference MS Excel application.
Dim oExcel
Set oExcel = CreateObject("Excel.Application")

'Turn-off alerts and messages for the MS Excel application object.
oExcel.DisplayAlerts = False

'Define an object to open and hold an Excel file/workbook.
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))

'Define an object to open and hold an Excel worksheet.
Dim oSheet
Set oSheet = oBook.Worksheets(1)

'Define an object and read and store the data from an Excel worksheet.
Dim oRange
Set oRange = oSheet.UsedRange

'Replace all the commas in the Excel worksheet object with    pattern "c1o2m3m4a5"
oRange.Replace ",", "c1o2m3m4a5"

'Save the edited Excel worksheet object into the CSV file 
'(CSV file name must be passed as the 2nd parameter when running this     script).
oBook.SaveAs WScript.Arguments.Item(1), 6

'Close the Excel file/workbook.
oBook.Close False

'Quit the MS Excel. End of the script.
oExcel.Quit