V. New to VBScript!

[mattshoreson]

Ok here goes, the basic architecture is as follows:

scheduler has a batch file with looks for an errorlevel of 0 or 1.

Batch file calls a vbs file.

vbs file runs a sql server stored procedure to place a file in a specified directory for subsequent pull of an ftp job.

Code for vbs files is as follows:

Option Explicit

Dim filePath, oExcel
Dim strComputer, objWMIService, colProcessList, objProcess

filePath = "C:\WINDOWS\Temp\test.xls"

Set oExcel = CreateObject("Excel.Application")

oExcel.Visible = True
oExcel.Workbooks.Open filePath
oExcel.Run("MyMacro")
oExcel.Quit()

Set oExcel = Nothing

strComputer = "."

Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcessList = objWMIService.ExecQuery ("Select * from Win32_Process " & "Where Name = 'Excel.exe'")

For Each objProcess in colProcessList

objProcess.Terminate()

Next

wscript.Quit()

My question is if an error is generated in the macro in the xls how do I get this value back to the vbs script so I can pass it in the quit command?

Cheers, Matt.

 

[mrmovie]

you say the vbscript is calling a stored procedure, all i can see is it calling a macro in an excel workbook, is this really required? why dont you have the code from the excel workbook just sat in your vbscript? makes it less complicated and you dont have to worry about getting the result from the macro call

 

[mattshoreson]

Thanks for your reply.

The vbscript calls the macro and the SP is called from the macro. Calculations and transformations are applied to the data and then returned to a sheet and saved as a csv.

These over-complicated steps are necessary due to the architec at work, limited access to the trading platform, scheduler requiring a success code, etc...

Ideally wouldn't have even done it this way but having to shoe-horn the process.

Managed to work it out anyway. Simply changed the macro from a sub to function and set the return value to be either a 1 or 0 dependent on errors using

myresult = objExcel.Run("test.xls!MyMacro")

thanks,matt.

 

[SkipVought]

Your macro ought to have error trapping. With such, it could quit under specified conditions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mattshoreson]

It does, that's my original point...

...The error trapping in the macro returns a 1 if error, 0 if not.

I wanted to reutrn this value to the vbs in order to use it in the wscript.quit() line.

The batch file the schduler runs can then query echo %errorlevel% for the flag.

 

[SkipVought]

The error trapping in the macro returns a 1 if error, 0 if not.

write the result to a log file.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mattshoreson]

Dont need to - changed the sub in the macro to a function to return the 0/1 flag.

Can use this flag in the line

e.g. wscript.quit(1)

then the batch can query for %errorlevel%

It's cool - I got it to work, it's clunky, but it works.

 

[mrmovie]

im in favour of 0 for success, 1 for failure (or any other non zero exit code, i know you get into 3010's and all the caper..) but, in vbscript i find it a dangerous game to play. one reason is cscript.exe and wscript.exe will return a 0 exit code if your script causes a runtime...so, care needs to be taken that you explicitly quit your script with Err.Number when appropriate. how this impacts your Function call from an excel workbook all depends on what you code looks like

 

[mrmovie]

>side note:
im working for an engineering function which use 1010 for success from vbscript. i can see why this is done, it means that if there is a runtime and cscript.exe and wscript.exe glibly give back a 0, this is seen as a 'failure'. This approach does have legs, i can see the benefit, and it proves itself in practice....i still dont like it though, it leads to nasty issues when using third party products as installation services (SCCM for instance doesnt like 1010 as a return code...)

 

[mattshoreson]

I used this as the source for errorlevel values.

http://www.rgagnon.com/wshdetails/wsh-0024.html

Incorrect?

 

[mattshoreson]

or what about defaulting to error on macro entry and then amending to success pre exit sub.

Would this capture the runtime?

 

[mrmovie]

having the default value of the function set to error, then only set it to success is of course a good idea.
this wouldnt implicitly catch a runtime though.

capturing a runtime could be achieved with the use of On Error Resume Next. this implies that it needs to be set globally (unless you are vain enough to think your code is bullet proof). things to consider with On Error Resume Next could be:

if you had On Error Resume Next set, and the aArray didnt contain more than one element then the If line would cause a runtime, therefore you would end up setting intReturn = 1, this would not be desirable.

If aArray(1) = "sell" Then
intReturn = 1
Else
intReturn = 0
End If

of course you can get round that by not having evaluations in your If statements, this would yield a more desirable result:

strResult = aArray(1)
If strResult = "sell" Then
intReturn = 1
Else
intReturn = 0
End If

you can also adopt,

If aArray(1) <> "sell" Then
intReturn = 0
Else
intReturn = 1
End If

 

[mrmovie]

the link you posted talks about:

wscript.Quit(2)

the danger is under what circumstance would that line of code not be executed? e.g. (a bad example)

Option Explicit
On Error Resume Next

Call Main()

Sub Main()
strX = "hello world"
Wscript.Quit(4)
End Sub

running this code does not yield a %errorlevel% of 4 at the command line, instead you get a 0

an alternative:

'this at least returns the runtime of 500, i have set On Error Resume Next at the top of the script, however, it doesnt cause Sub Main to behave as it has On Error Resume Next on

Option Explicit
On Error Resume Next
intReturn = 666
Call Main()
If Err.Number <> 0 Then
Wscript.Quit Err.Number
Else
Wscript.Quit intReturn
End If

Sub Main()
strX = "hello world"
intReturn = 4
End Sub

 

[mrmovie]

whats the best approach for you? testing i would say. deliberately cause a runtime in your macro,,,check that you get the desired return codes or that it is flagged upstream as a failure