Salesforce

Microsoft Excel - Methods (Magic xpi 4.5)

« Go Back

Information

 
Created ByKnowledge Migration User
Approval Process StatusPublished
Objective
Description

Microsoft Excel - Methods

Use the following methods to configure the Microsoft Excel component:

The following table lists the methods and parameters that can be used. Parameters in bold are mandatory.

Name

Parameters

Description

Cell executes operations on an active cell.

Action

The action that you want to take. Select one of the following from the drop-down list:

  • SetValue: Sets the cell value according to the value field.

  • GetValue:- Gets the cell value into the value field.

  • SetFormula: Sets the cell as a formula according to the value field.

  • GetFormula: Gets the cell formula into the value field.

  • SetProperties: Sets the cell's properties.

  • Clear: Clears the cell content.

  • GetActiveRowColumn: Returns the current cell position.

  • GetLastRowColumn: Returns the last cell (with a value) to a position in the sheet.

Row

The cell row.

Column

The cell column.

Value

The cell value.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

Cell Format lets you format a single cell or a range of cells. If the row and the column > 0, the format is for a single cell. Otherwise, the format is for a range of cells.

RangeFromCell

The range from the cell.

RangeToCell

The range to the cell.

BorderColor

The cell border color.

BorderLineStyle

The cell border line style.

Font

The font that you want to use.

FontSize

The font size that you want to use.

FontColor

The font color that you want to use.

FontBGColor

The font background color that you want to use.

FontStyles

The font style that you want to use. Select one of the following from the drop-down list:

  • Bold

  • -Bold

  • Underline

  • -Underline

  • Italic

  • -Italic

  • Outline

  • -Outline

  • Shadow

  • -Shadow

  • Strikethrough

  • -Strikethrough

Horizontal

Lets you select the horizontal format that you want to use. Select one of the following from the drop-down list:

  • General

  • Left

  • Right

  • Center

  • Fill

  • Justify

  • Distributed

HorizontalIndent

Defines the horizontal indent of the cell or range of cells. Select a number from 1 to 15 from the drop-down list.

Vertical

Lets you select the vertical format that you want to use. Select one of the following from the drop-down list:

  • Top

  • Center

  • Bottom

  • Justify

  • Distributed

OrientationDegrees

Defines the degree of orientation of the cell or range of cells, from -90 to 90 degrees.

Control

Enables you to control the format of the cell or the range of cells. Select one of the following from the drop-down list:

  • Wraptext

  • -Wraptext

  • Shrinktofit

  • -Shrinktofit

  • Mergecells

Direction

Lets you set the format direction. Select one of the following from the drop-down list:

  • Context

  • Left to Right

  • Right to Left

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

Chart lets you create a chart.

Type

The chart type. Select Bar, Column, or Pie from the drop-down list.

RangeFromCell

Lets you define the range from the cell.

RangeToCell

Lets you define the range to the cell.

ChartTitle

Lets you enter the title of the chart.

XTitle

Lets you enter the title for the X-axis.

YTitle

Lets you enter the title for the Y-axis.

PlotBy

Lets you plot by rows of columns. Select one of the following from the drop-down list:

  • Rows

  • Columns

Location

Lets you define the location of the chart. Select one of the following from the drop-down list:

  • New Sheet

  • Sheet

LocationName

Lets you name the location of the chart.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

Convert CellRange translates cell pointers from row/column to range, and vice versa.

Convert Type

The type of conversion that you want to execute. Select one of the following from the drop-down list:

  • Cell2Range

  • Range2Cell

CellRow

Identifies the cell row that you want to translate. This is mandatory if you select Cell2Range in the Convert Type parameter.

CellColumn

Identifies the cell column that you want to translate. This is mandatory if you select Cell2Range in the Convert Type parameter.

RangeCell

Defines the cell's range address that you want to translate. This is mandatory if you select Range2Cell in the Convert Type parameter.

ErrorCode

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

List sets, gets, or deletes an entry from a list table. This is an internal table which is stored in the memory.

Action

The action that you want to take. Select one of the following from the drop-down list:

  • Set

  • Get

  • Delete

Number

This parameter is the record number in the list table. If this parameter is left blank, or defined as 0 in conjunction with the Delete action, the complete list table will be deleted.

Value

This parameter is valid only for Set or Get actions.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

List to XML outputs the list table records in an XML file format.

XML File Name

The XML file that is created as a result of the last GetList action. The User BLOB contains the XML result.

Source File Name

This is used for the last GetList action.

Sort By

Defines the order of the created output. Select one of the following from the drop-down list:

  • Number

  • Row

  • Column

XML Result

This parameter returns the output in XML format.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

Macro lets you activate a macro.

MacroName

Lets you identify the macro that you want to activate.

Error Code

The operation error code.

Click to open the Variables List. Select a variable or user parameter where the error code is returned, and then click Select.

Printer lets you Get or Set an active printer.

Action

The action that you want to take. Select one of the following from the drop-down list:

  • SetPrinter

  • GetPrinter

Printer

Lets you select the printer that you want to use.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

Quit lets you quit Excel. It is recommended to use this method at the end of processing to remove Microsoft Excel from the Windows Task Manager.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

Range lets you activate operations on an active range.

Action

The action that you want to take. Select one of the following from the drop-down list:

  • SetValue: Sets the cell value according to the value field.

  • GetValue:- Gets the cell value into the value field.

  • SetInternalVector: Sets the Internal vector data into the range. This should be a line range; for example, A3 to F3. You should use the vector methods to work with the vectors.

  • GetInternalVector: Gets the range data into the Internal vector. This should be a line range; for example, A3 to F3. You should use the vector methods to work with the vectors.

  • SetGridVector: Sets the grid vector data into the range. This can be a multi-line range; for example, A1 to G8. Use the vector methods to work with the vectors.

  • GetGridVector: Gets the range data into the Internal vector. This can be a multi-line range; for example, A1 to G8. Use the vector methods to work with the vectors.

  • Select: Selects the defined range.

  • Delete: Deletes the defined range.

  • Clear: Clears the defined range content.

  • Insert: Inserts a line or column according to what is defined in the value field (RIGHT to insert column or DOWN to insert row).

  • AutoFit: Automatically fits the cells in the defined range.

  • AllowEditRange: Allows you to edit the defined range cells on a protected worksheet.

  • GetList: Gets the worksheet content into a memory table, which can be used with the List to XML method.

RangeFromCell

Lets you define the range starting cell.

RangeToCell

Lets you define the range ending cell.

RangeName

Lets you define the range's name. This is mandatory if you do not use RangeCell parameters.

Value

The value of a single cell.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

Vector Clear clears all entries in the Internal vector.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

Vector GetEntry gets a value from the Internal vector entry, up to 1000 characters.

Index

The vector entry index.

Value

The value variable.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

Vector GetSize gets the number of entries in the Internal vector.

Size

The vector size.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

Vector SetEntry lets you set a value in the Internal vector entry, up to 1000 characters.

Index

The vector entry index.

Value

The value of the variable.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

VectorGrid Clear lets you clear all entries in the Grid vector.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

VectorGrid Get Entry gets the Internal vector from a Grid vector entry.

Index

The vector entry index.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

VectorGrid GetSize gets the number of entries in the Grid vector.

Size

The vector size.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

VectorGrid SetEntry sets the Internal vector in a Grid vector entity.

Index

The vector entry index.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

Workbook Close lets you close the workbook.

Save Changes

Lets you save any changes when you close the workbook. Select one of the following from the drop-down list:

  • No

  • Yes

FileName

The full path file name that you want to save. This is available only if you select Yes in the Save Changes field (above).

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

Workbook Open lets you open an Excel workbook. If a workbook does not exist, a new one will be created.

FileName

The full path name of the xls file, including the .xls extension. For example, My Documents\file1.xls

ReadOnly

Lets you open the file in read-only mode. Select No or Yes from the drop-down list.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

Workbook Print lets you print the workbook.

ActivePrinter

Lets you define the printer name. If you do not define the printer name, the default printer will be used.

Copies

Lets you select how many copies you want to print.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

Workbook Protect lets you protect or unprotect the workbook.

Protect

Lets you protect the workbook by locking it. Select No or Yes from the drop-down list.

Password

Lets you define a password to protect the workbook.

The entered password will be masked by dots.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

Workbook Save lets you save the workbook using the Save or Save As functions.

Action

The action that you want to take. Select one of the following from the drop-down list:

  • Save

  • Save As

FileName

The full path name of the xls file, including the .xls extension. For example, My Documents\file1.xls

This field is available only if you select Save As in the Action field (above).

FileFormat

The format that you want to save the file in. Select one of the following from the drop-down list:

  • XLS

  • XLSX

  • CSV

  • HTML

  • XML

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

Worksheet executes an operation on a worksheet. (You should use either the Number parameter or the Name parameter to define the worksheet).

Action

The action that you want to take. Select one of the following from the drop-down list:

  • Activate: Activates the required worksheet.

  • SetName: Sets a name to the worksheet.

  • GetName: Gets the worksheet name.

  • Create: Creates a new worksheet.

  • CopyBefore: Copies the worksheet and puts it before the worksheet named in the name_password_printer field.

  • CopyAfter: Copies the worksheet and
    puts it after the worksheet named in the name_password_printer field.

  • CopyToStart: Copies the worksheet and puts it first.

  • CopyToEnd: Copies the worksheet and puts it last.

  • Delete: Deletes the worksheet.

  • Print: Prints the worksheet. Use the name_password_printer field to define the printer name. Leave it empty to use the default printer.

  • Protect: Protects the worksheet. Use the name_password_printer field to define the password.

  • UnProtect: Unprotects the worksheet. Use the name_password_printer field to define the password.

Number

The worksheet number. If you do not define this, you should use the Name parameter instead.

Name

The worksheet name. If you do not define this, you should use the Number parameter instead.

Copies

Defines the number of copies that you want to produce, if you selected Print in the Action parameter above.

Name_Password_Printer

This variable lets you execute multiple actions.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

Worksheets Count lets you automatically count the number of worksheets that you are using.

Counter

Counts the number of worksheets.

Error Code

The operation error code.

Click to open the Variables List. Select a variable where the error code is returned, and then click Select.

Related Topics

How to Use Direct Access Methods

Reference
Attachment 
Attachment