topic RE: How to ensure "Run macro" executed successfully in Product Forum

How to ensure "Run macro" executed successfully

Bhavithra — Tue, 14 Mar 2023 11:04:00 GMT

I am using "Run macro" action in the Excel VBO to call the macro code in the excel sheet. I just want to ensure if the macro was executed successful or not.

------------------------------
Bhavithra L
------------------------------

RE: How to ensure "Run macro" executed successfully

RamónRequena_L1 — Tue, 14 Mar 2023 12:41:00 GMT

Hi Bhavithra,

I would advise you to build an Error Handler in the Macro that generates an ErrorLog.txt should the macro fail and then parse the File via BP. Something like this:

Public Function LogFile_WriteError(ByVal sRoutineName As String, _
                                   ByVal sMessage As String)
Dim sText As String
   On Error GoTo ErrorHandler
   If (g_objFSO Is Nothing) Then
      Set g_objFSO = New FileSystemObject
   End If
   If (g_scrText Is Nothing) Then
      If (g_objFSO.FileExists("<YourPath>\errorLog.txt") = False) Then
         Set g_scrText = g_objFSO.OpenTextFile("<YourPath>\errorLog.txt", IOMode.ForWriting, True)
      Else
         Set g_scrText = g_objFSO.OpenTextFile("<YourPath>\errorLog.txt", IOMode.ForAppending)
      End If
   End If
   sText = sText & "" & vbCrLf
   sText = sText & Format(Date, "dd.MM.yyyy") & "-" & Time() & vbCrLf
   sText = sText & " " & sRoutineName & vbCrLf
   sText = sText & " " & sMessage & vbCrLf
   g_scrText.WriteLine sText
   g_scrText.Close
   Set g_scrText = Nothing
   Exit Function
ErrorHandler:
   g_scrText.WriteLine sText
   g_scrText.Close
   Set g_scrText = Nothing
 
End Function

You must declare two global variables in order for this to work:

Public g_objFSO As Scripting.FileSystemObject
Public g_scrText As Scripting.TextStream

The <YourPath> tag should be replaced by your process folder or wherever you want to store the ErrorLog, and this should be accessible to both the Macro and Process.

I hope it helps

------------------------------
Ramón Requena López
RPA Developer
Magenta Telekom
------------------------------

RE: How to ensure "Run macro" executed successfully

Joshna_16 — Wed, 15 Mar 2023 05:56:00 GMT

Hi Bhavithra,

Add error handling within macro with message box

On Error GoTo Ermsg

Ermsg:

MsgBox "The following error occurred:"&Err.Description

------------------------------
Joshna Dammala
RPA Developer
Asia/Kolkata
------------------------------

RE: How to ensure "Run macro" executed successfully

ThomasHutchins — Wed, 15 Mar 2023 14:13:00 GMT

Hi,

I define two ranges on a specific sheet in the Excel workbook: MacroInput (used to pass input values to the macro), and MacroResult (used to find out if the macro completed successfully, or, if not, what the error description from Excel is). The macro reads the MacroInput range if necessary to get a date, filepath, etc. If it completes without errors the macro will write Success in the MacroResult range, which is then read by the BP process. If an error accurs, the macro writes the Excel error description to MacroResult. Here is an example:

Public Sub Import_AP_Data()
Dim MyPath As String
MyPath = ThisWorkbook.Sheets(MainSht).Range("MacroInput").Value
On Error GoTo IAPDerr

(do stuff)

ThisWorkbook.Sheets(MainSht).Range("MacroResult").Value = "Success"
cleanup:
On Error Resume Next
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Exit Sub
IAPDerr:
ThisWorkbook.Sheets(MainSht).Range("MacroResult").Value = Err.Description
GoTo cleanup
End Sub

The macro must not have any messageboxes or other popups.

Hope this helps,

Hutch

------------------------------
Thomas Hutchins
Lead Developer
Discover
America/Chicago
------------------------------