MicrosoftExcel Connector

Use the MicrosoftExcel connector to integrate Excel data, functions and events into a solution. You can use the connector in automations to write data to cells, read data from cells, as well as importing and exporting sets of data from Excel.

When adding this component to an automation, it is placed in the Global tab of the component tray by default. See the Adding Global Components to an Automation for more information on the Global tab. This component must be Global to be referenced by other automations in the project.

See the General Component Properties Methods Events topic for additional information.

Properties:

 

Property

Description

Changes

Specifies the save changes action taken when the workbook is closed. The options are:

  • Save - Changes are saved when the workbook is closed

  • DoNotSave- Changes are not saved when the workbook is closed

 

Mode

Specifies how the workbook file should be opened. The options are:

  • Create

  • Open

When set to Create (Default) OpenSpan Studio will create a file if it did not previously exist.  The Open setting ensures that a file will not be created, but will try opening the designated file instead.  If there is no document present when OpenSpan Studio tries to open it, the system will show an error message.

Visible

 A Boolean property that indicates whether or not the main Excel window is visible during solution runtime.  

Workbook

Use the Workbook property to specify the name of the workbook file you want to use in the solution.  You must specify a workbook to use the component.  By default, OpenSpan will look at the exact file path selected.  However, the Folder property allows you to determine the folder file path that is resolved at runtime using predefined tokens.  You select these tokens from a drop down menu with options such as Deployment, Temporary, Personal etc.  The folder file path is then resolved by the selected token instead.

 

Worksheet

Use the worksheet property to specify the worksheet in the workbook that you want to use in the solution. If you do not enter a worksheet, the default sheet will be used.

ExcelApplication

Microsoft Excel COM Instance

ExcelWorkbook

Microsoft Excel COM Instance.

IsRunning

This property indicates whether or not Excel is running.

SupportedName

This property indicates the corresponding Office name for the Excel program used.

SupportedVersion

Displays the version number of the Excel program used.

Version

Indicates the current running version of Microsoft Excel.

 

Events:

 

Event

Description

NewSheet

This event occurs before a new workbook sheet is created.

NewWorkbook

This event occurs when a new workbook is created.

Quit

This event occurs when Microsoft Excel is closed manually.

SheetActivate

This event occurs when a workbook sheet is activated.

SheetCalculate

This event occurs when the current workbook is recalculated.

SheetChange

This event occurs when the current workbook sheet changes.

SheetDeactivate

This event occurs when the current workbook sheet is deactivated.

WindowActivate

This event occurs when the main Excel window is activated.

WindowDeactivate

This event occurs when the main Excel window is deactivated.

WorkbookClosed

This event occurs when the current workbook is closed.

WorkbookOpened

This event occurs when a new workbook is opened.

WorkbookPrinted

This event occurs when the current workbook is printed.

WorkbookSaved

This event occurs when the current workbook is saved.

 

Methods:

 

Method

Description

Parameters

Result Type

Close

Closes a currently open workbook.

None

Void

ExportData

Exports current workbook into .NET data table (row and column are 1-based). A header is used for search parameters.  If set to true, the table creates headers for the table.

String cellStart, String cellEnd, Boolean createHeader

DataTable

GetCellValue

Returns the cell indicated by the input parameter.  You can select a specific cell (such as d4) or input the cell number from another object. You can also use a named value as the input. See the notes on Named Value below.

String cell

String

ImportData

Imports .NET data table into current workbook (row and column are 1-based). A header is used for search parameters.  If set to true, the table creates headers for the table. Column counting begins at 0 and row counting begins at 1.

 

Boolean CreateHeader, Int32 Rowstart, Int32 columstart, dataTable

Void

Open

Opens a new workbook using the supplied file name.

String Workbook

Void

RunMacro (1 Parameter)

Runs a Visual Basic macro or calls a function.

 

String MacroName

Object

RunMacro (6 Parameters)

Runs a Visual Basic macro or calls a function.  All param1-param5 are optional.  

String Macroname, Optional Parameters

Object

Save

This method saves the currently opened workbook.  It will overwrite an existing file.

 

None

Void

SaveAs

This method saves the currently opened workbook under a new file name.  A name must be selected or the method will produce an error.

String filename

Void

SetCellValue

This method sets the current workbook cell value. In addition to referencing the cell directly, you can use a named value as the input. See the notes on Named Value below.

 

String cell, String cellValue

Void

 

Named Value

If you set cell value to a named value (Insert, Name, Define, ADD) in a spreadsheet and use that in GetCellValue and SetCellValue (instead of, for example 'B23'), then you don’t have to hard code cells numbers in your automations.  When the named value represents a formula calculation result, it returns the appropriate value for the selected range.  If there is no formula, GetCellValue will not iterate through that result cell range due to the unexpected format.

 

See also

Using the MicrosoftExcel Connector