Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

How to ensure "Run macro" executed successfully

BhavithraL
Level 3

‎14-03-23 11:04 AM

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
------------------------------
0 Likes
3 REPLIES 3

RamónRequena_L1
Level 5

‎14-03-23 12:41 PM

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
------------------------------

Joshna_16
Level 4

‎15-03-23 05:56 AM

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
------------------------------
0 Likes

ThomasHutchins
Level 3

‎15-03-23 02:13 PM

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
------------------------------
0 Likes