Skip to main content

What can we help you with?

Search

Execute Macro

Sergei Ostapov
  • Updated

Description

This activity requires having Microsoft Excel installed on the computer.

This activity allows you to execute a .vbs-macro in the selected Excel file.  Studio Pro supports Microsoft Excel .xls and .xlsx file formats.

Ensure that the “Enable all macros” and “Trust access to the VBA project object model” options are enabled in the Trust Center of Microsoft Excel before executing the macro. 

TRUST_CENTER.png

As of the 8.3 update, there are two ways of interacting with MS Excel - Microsoft Native and Generic. Navigate to the menu "Settings"  → "Microsoft activities" to choose a method.

Microsoft Native uses standard Microsoft methods to interact with the Excel application. It requires Excel to be installed on the machine and provides higher efficiency and stability with different types of files.

Generic does not require Excel to be installed, but it may be less efficient and stable.

 

Parameters

Path

  1. Set a value option allows you to set a path to the Excel file where the macro is to be executed. By clicking PICK you can find the desired file using file explorer.  
  2. Calculate a value option lets you use a special formula or method to set a path to the required file. 
  3. Save the previous step result option takes the path to the required file from the result of the previous step in the workflow.

Please note that if the file name contains spaces, then the whole path to the file should be marked with quotes. For example:
"C:\Users\John\Documents\ElectroNeek\Macros\Macro for invoices"
Another option is to replace all the spaces with the _ symbol. In such a case, you will not need to mark the path with quotes.

 

Macro file path

  1. Set a value option allows you to set a path to the .vbs file (macro) manually. By clicking PICK you can find the desired file using file explorer.  
  2. Calculate a value option lets you use a special formula or method to set a path to the .vbs file. 
  3. Save the previous step result option takes the path to the .vbs file from the result of the previous step in the workflow.

Please note that if the file name contains spaces, then the whole path to the file should be marked with quotes. For example:
"C:\Users\John\Documents\ElectroNeek\Macros\Macro for invoices"
Another option is to replace all the spaces with the _ symbol. In such a case, you will not need to mark the path with quotes.

 

Sub or Function name

  1. Set a value option allows you to manually set the name of the procedure or function to be launched. 
  2. Calculate a value option lets you use a special formula or method to set the name of the procedure or function. 
  3. Save the previous step result option takes the name of the procedure or function from the result of the previous step in the workflow.

 

Input arguments

This parameter allows you to pass certain arguments to the macro, to the Sub or Function defined above. The arguments should be listed as an array of strings or numbers. For example, if a macro function or procedure takes four input arguments, you need to pass the value in this parameter through an array of numbers (or strings) in the same order - [2,3,4,5] (here, number 2 will be passed to the first argument of the procedure or function, number 3 to the second, and so on; numbers are given for example).

 

  1. Calculate a value option lets you set the array of strings or numbers to state the input arguments. 
  2. Save the previous step result option takes the input arguments from the result of the previous step in the workflow.

 

Comment

This parameter allows you to create an annotation for the activity. The input text will be displayed above the activity name.

 

How to use it?

The "Execute macro" activity can be useful for users with experience in working with macros: it allows you to quickly solve non-trivial problems by reusing the existing code.

 

When working with macros in MS Excel, there are several approaches, for example, the built-in Excel functionality involves creating macros, in which case they will be saved in .xlsm format. On the other hand, you can create macros separately from Excel using VBScript. Such files will have the format .vbs. Studio Pro supports such macros.

 

In order for the activity to work correctly, you must first open Excel with a command in the .vbs file. For example, you can use this method:

Dim xlApp, Book
Set xlApp = CreateObject("Excel.Application")
Set Book = xlApp.Workbooks.Open(WScript.Arguments(0))

 

You can then specify other instructions to open the file.

After executing the macro, you must save the page and close Excel. Here is an example of such a command:

Book.Save
Book.Close True
xlApp.Quit

 

Here is an example of a VBS file. This script will change the size of the range A18:A (to the end of the page). The height will be 65 and the width will be 14.

Private Sub RowSize_Click()
      Dim xlApp, Book, Sheet
      Set xlApp = CreateObject("Excel.Application")
      Set Book = xlApp.Workbooks.Open(WScript.Arguments(0))
      Set Sheet = Book.Sheets("Sheet1")
      Sheet.Range("A18:A" & Sheet.Rows.Count).RowHeight = 65
      Sheet.Columns("A:Z").ColumnWidth = 14
      Book.Save
      Book.Close True
      xlApp.Quit
End Sub

 

Here are two simple examples of how ElectroNeek interacts with macros. At the end of this article, you will find two zip archives for downloading. These archives contain the .neek file to run in Studio Pro, the .vbs macro file linked inside the .neek file, and the .xlsx file, the table in which changes occur as a result of the bot's actions.

 

The sample_macro.zip archive demonstrates ElectroNeek's interaction with a single procedure (Sub) macro. The macro changes the color of cells B2:B5 to blue in sample_file.xlsx (initially the cells have no background color). 

 

The second example, sample_macro_function.zip, does the same thing as the first one, but with the following differences: 

First, the macro itself uses a Function, not a procedure. 

Second, the Function returns a result, the string "DONE". This result is stored in the variable x in the .neek file. So after running the .neek file, you have to make sure that the color of the cells B2:B5 is changed to blue and the string "DONE" is displayed in the variable x.

 

Thus, these examples demonstrate how parameters are passed to a macro and how the result can be returned from the macro and saved in a .neek file.

Related articles