Error Level of Excel macro to Batch script

[Shalih]

Can any please let me know how to set an error level in excel macro. here is the scenario. I have an ecel that runs automatically from the scheduled task which runs a few macros. this excel sheet is initiated with in a batch script. If the macro returns an error or fails, i need to get an error level returned back to the script which can trigger a mail. Please help me in setting an error level inside the macro when the excel fails and let it return to the batch script.

Thanks!

 

[Ruddles]

Write the macro as a function and return the errorlevel from the function. As an example, this function will error if the string passed to it is empty:-

Option Explicit
 
Public Function MyFunction(ByVal sParameter As String) As Boolean
 
  If Len(sParameter) = 0 Then
    MyFunction = False
  Else
    MyFunction = True
  End If
 
End Function

Then instead of merely calling the macro as a procedure, you call it as a function and test its value:-

  If MyFunction(strValue) Then
[COLOR=black]    MsgBox "Okay"[/COLOR]
  Else
    MsgBox "Error!"
  End If

Obviously I've just put dummy comments in there but it gives you an idea of how to do it.

If you need to return a range of possible values from the function instead of just True/False, you'd do something like this:-

Option Explicit
 
Public Function MyFunction(ByVal Parameter As [I]{type}[/I]) As Integer
 
  If [I]{some test fails}[/I] Then MyFunction = 1 : Exit Function
 
  If [I]{some other test fails}[/I] Then MyFunction = 2 : Exit Function
 
  If [I]{final test fails}[/I] Then MyFunction = 3 : Exit Function
 
 [COLOR=green] ' passed all tests - flag as okay[/COLOR]
  
  MyFunction = 0
 
End Function

then you'd test the value of the function as a number: 0 is okay, 1-3 indicate an error.

 

[Shalih]

R. thanks for the prompt reply. I am running a macro which calls a lot of functions inside it. Now i need to pass this value to a notepad file.

 

[Ruddles]

This will create (or overwrite) a file. Insert your desired filename in place of the red bit and whatever value(s) you want to write to the text file in place of the blue bit.

dim intfh as integer
close
intfh=freefile()
open [COLOR=red][B]"c:\temp\filename.txt"[/B][/COLOR] for output as #intfh
print#intfh,[B][COLOR=blue]"some value"[/COLOR][/B]
close #intfh

Output multiple values separated by semicolons:-

print#intfh,format(now(),"dd/mm/yyyy hh:nn:ss");"  ";[I]somevariable[/I]

 

[Shalih]

Thanks R... hope this works.. let me give it a try and will let you know...

 

[Shalih]

R.. it would take another 8 hours before i test this ... but a doubt.. within the macro, i am suppressing the ON ERROR statement in some parts.. Will this still fetch the desired result... I cant remove the suppression as it would cause issues to the existing functionality...

again as a summary, if the macro returns a 'debug/end' message with any kind of error valu; this is what i need to capture... becuase i cant let the excel sheet not do its task and me sit idle without the checking on the server....i needed this value in a notepad file so that i can compare and shoot a mail using the batch script....

Will be really great for your quick response!! i've been banging my head for 2 days on this and i am a newbie in this VBA stuff..

 

[Ruddles]

The function only returns a value indicating how it completed. It doesn't raise an error. You have to check the return value and take whatever action you want to.

 

[Shalih]

finally i compromised with the DOS scripting