Executing Excel Maros through UFT script and capturing error/execution status of Macro.
I am developing a script in UFT that is to run Macro from excel sheets.
I am using “excelobject.run” to invoke the Macro and this working fine Macros are getting executed.
The flow is open excel sheet using excel object -> make excel doc visible -> Execute Macro using
- The next thing I am planning to do is capture error or Macro execution status in UFT.
Means after invoking macro if any error occurs during Macro execution the error should get caught by the UFT script and update result column in data sheet with the status.
Similarly capture the status of Macro execution completed and update result column in data sheet with the status
I tried with On Error Resume Next and next line called a function which will capture the err. Description and set in the result column of data sheet.
But UFT is not able to get the error description.
- Other thing is to click on a POP UP that is coming up as part of macro execution and continue the script.
Scenario : in one of the sheet at the end of Macro execution it is throwing a POP Up which indicates macro execution completed successfully. To continue script execution One need to click on the POP Up. The script is not able to handle that part.
My understanding towards that is once macro is invoked by “excelobject.run” command the control is going to excel process and will come back to UFT script only after the Macro execution completed. Due to this my POP handling code is not able to handle POP up that is appearing at the end of Macro execution as the control is not transferred back to UFT script
Re: Executing Excel Maros through UFT script and capturing error/execution status of Macro.
Here you go 🙂
1) To return the result from the Macro function to UFT.
Here's an example.
'Macro Function Function GetFunctionResult() Dim A As Integer On Error GoTo Error_handler: 'your code 'your code GetFunctionResult = "Passed" Exit Function Error_handler: GetFunctionResult = "Failed" End Function 'Run Macro & Get result Set objExcel = CreateObject("Excel.Application") Set objWorkbook = objExcel.Workbooks.Open("C:\ExcelFiles\ErrHandler.xlsm") ReturnVal = objExcel.Run("GetFunctionResult") MsgBox ReturnVal
2) To handle Macro popup?
You have to disable all the pop ups in Macro. Comment out all 'MsgBox' functions. Or use flag & check if it's Manual or Automated run.