Run Macro Office

Runs a macro defined in an Office application opened by the IBM RPA Studio.

Command availability: IBM RPA SaaS and IBM RPA on premises

Script syntax

IBM RPA's proprietary script language has a syntax similar to other programming languages. The script syntax defines the command's syntax in the script file. You can work with this syntax in IBM RPA Studio's Script mode.

officeMacro --macro(String) --officeapplication(OfficeApplication)

Dependencies

  • To use this command, open an Office file with the Open Office file (officeOpen) command.

  • Microsoft™ Office 2010 or higher is required.

  • Ensure that the architecture (bitness) of your Office apps, IBM RPA, and your operating system match. For example, if you are using a 64-bit operating system, IBM RPA and Office must be 64-bit too.

To use this command, you must enable macros in Excel. The Following procedure shows you how to enable macros:

  1. Open Excel.
  2. Click the File tab.
  3. Click Options.
  4. Click Trust Center.
  5. Click the Trust Center Settings button.
  6. Go to Macro Settings and select Enable VBA macros (not recommended; potentially dangerous code can run).
  7. Click Ok and close the application.
Important:
  • Enabling this option can make your machine vulnerable to malicious code. Ensure that you only run safe macros with IBM RPA.
  • Ensure that the VBA project that contains the macro is not password encrypted.

Input parameters

The following table displays the list of input parameters available in this command. In the table, you can see the parameter name when working in IBM RPA Studio's Script mode and its Designer mode equivalent label.

Designer mode label Script mode name Required Accepted variable types Description
Macro macro Required Text Macro to run on the Office file.
Office Application officeapplication Required Office Application Variable that stores the Office Application instance.

The macro parameter

Macros with the End statement placed anywhere in the procedure can cause the Run Macro Office (officeMacro) command to throw the 0x800A9C68 error. It occurs because the End statement forces the entire application to stop running, which also affects the runtime of the Run Macro Office (officeMacro) command.

To handle this scenario, use the Handle Error (onError) command to handle the 0x800A9C68 error when it appears during the script runtime. For reference, see the examples in the Example section.

Example

In the following example, the Run macro Office (officeMacro) command runs a macro to sum the values in the cells "A1" and "B1" and add the result in the cell "C1":

defVar --name ID --type Numeric
defVar --name officeApplication --type OfficeApplication
// Download the following file to run this command.
officeOpen --path "Sample file" --type "Excel" --keepvisible  officeApplication=officeapplication ID=processid
officeMacro --macro "Range(\"C1\").Value = WorksheetFunction.Sum(Range(\"A1:B1\"))\r\n" --officeapplication ${officeApplication}
// Consider that the Excel file that you use as a sample file has the value "1" for "A1" and "2" for "B1". The macro adds 1+2 from the A1 and B1 cells, saving the result in C1.
delay --timeout "00:00:30"
// Waits 30 seconds to close Excel.
officeClose --officeapplication ${officeApplication}
// When looking at the opened Excel file, you should see the following result:
// |A1|B1|C1|
// |1 |2 |3 |

In the following example, the Run macro Office (officeMacro) command runs a macro that contains the End statement in its procedure, what causes the command to throw the 0x800A9C68 error. The Handle Error (onError) command is placed in the script to handle this error and avoid the abrupt stop of the script:

defVar --name officeApplication --type OfficeApplication
onError --label excelError
// Consider a sample Excel file that contains the value "1" for "A1" and "2" for "B1". The macro sums these values and saves the result in "C1".
officeOpen --path "Sample file" --type "Excel" --keepvisible  officeApplication=officeapplication
// The macro validates if the cell A1 has the value 1, and if it's true, it runs its code block which ends the loop with the End statement.
officeMacro --macro "If Range(\"A1\").Value = 1 Then\r\n    Range(\"C1\").Value = WorksheetFunction.Sum(Range(\"A1:B1\"))\r\n    MsgBox \"The sum of A1 and B1 is \" & Range(\"C1\").Value & \". Ending the If statement with the End statement.\"\r\n    End\r\nElse\r\n    MsgBox \"The current value of A1 is \" & Range(\"A1\").Value\r\nEnd If" --officeapplication ${officeApplication}
officeClose --officeapplication ${officeApplication}
beginSub --name excelError
	if --left "${rpa:error.Message}" --operator "Contains" --right 0x800A9C68
		logMessage --message "Recovering from the 0x800A9C68 error." --type "Info"
		recover
	endIf
endSub

Limitations

  • The command works only with .xlsm files.
  • The command cannot run Macros that are inside a password-encrypted VBA project.