Excel Macro

The Excel Macro node allows you insert into and execute VBA script on an Excel document.

Properties

Connection

FilePath
Type: String
The path to the Excel workbook.

ReuseExcelApplication
Type: Boolean
When true, the Excel application object will be kept open for the lifetime of the calling workflow. This provides a significant performance benefit.

ReuseExcelWorkbook
Type: Boolean
When true, the Excel workbook object will be kept open for the lifetime of the calling workflow unless the FilePath property is changed. This provides a significant performance benefit. When set to true, take care that setting range values does not affect the workbook state in a way that makes it unreliable for subsequent calls.

Macro

Type: Multiline Text Input
The Macro property allows you to specify Excel VBA Script that is injected into an Excel Document and executed.

Note You may only specify a single Function/Sub body. You must not define the Function/Sub as part of the Macro.

MacroParametersXml

Type: Multiline Text Input
The Macro Parameters property allows you to insert parameters from Flowgear into the Macro.
Below is an example of what the Xml should look like.

<parameters>
	<sheetname>"MySheet"</sheetname>
	<range>"A1D5"</range>
</parameters>

Element Names -Each child element of the root defines the parameter name and the elements value is the parameter value. You can use the parameter name anywhere in the Macro property.

String Values -String values must be encapsulated with double quotes (").

Remarks

This Node permits re-use of Excel as a calculation engine and is particularly useful when applying custom business logic that can be owned by the end-user. This Node must be run at a DropPoint and FilePath should be set to a location that is accessible to the DropPoint. As a result, note that Excel needs to be installed on the same server as the DropPoint.

For greatest efficiency, ReuseExcelApplication and ReuseExcelWorkbook should be set to True. ReuseExcelApplication causes the Connector to create an Excel application instance only once for the lifetime of the workflow. ReuseExcelWorkbook causes the Connector to load the Workbook only one for the lifetime of the workflow.

Setting up a connection

In order for the DropPoint to have permission to use the Excel libraries, it must be running under a user account and not the System account. Change this setting from Services by right-clicking the service, choosing Properties and clicking the Log On tab. Additionally, the user specified must be able to open the Excel document specified in the Connection.

If you receive an error accessing the document, ensure that both of these folders exist:

C:\Windows\SysWOW64\config\systemprofile\Desktop
C:\Windows\System32\config\systemprofile\Desktop (this folder is required for Windows 8)

Additionally, ensure that the Desktop folders have full read/write permissions for the relevant user.

If you receive an error, "The workbooks VBA project cannot be accessed."

Open the Excel document (as administrator) and go to File->Options->Trust Center
Click on the 'Trust Center Settings' button at the bottom of the page and then select the 'Macro Settings' tab.
Make sure the following segments are enabled:

Enable all macros (not recommended; potentially dangerous code can run)
Trust access to the VBA project object model

Excel document saving

After a macro is executed on an Excel document, the document is saved. Take care not to overwrite any import data that may exist in the document.

Examples

See Sample Workflow for an example.

In the Connection, provide a path to a spreadsheet that looks like this:

After executing the sheet against the Node, the resultant spreadsheet should look like this: