Excel Commands


 Executes a command that is document-specific, such as retrieving the value from a spreadsheet cell.

The available commands are:

  Create Workbook
  Open Workbook
  Save Workbook
  Close Workbook
  Get Cell
  Set Cell
  Select Cell
  Get Cell Format
  Set Cell Format



  Create Workbook
 

Creates a new Excel spreadsheet document (.XLS). If data is provided, this spreadsheet will be native Excel 3.0 format, which ensures that the generated XLS file will be backward compatible with all existing Microsoft Office installations (it can be easily re-saved in the current format). This design also allows a new spreadsheet to be created even if Microsoft Office or Excel is not installed on the PC. This is important when this action is used inside of a compiled EXE that is distributed to computers without Excel.

If no data is provided, a blank spreadsheet is created in the current version of the Microsoft Office installation. You may then add data to the spreadsheet using the Set Excel Cell command to fill in the data. Therefore, this provides an alternative method for creating a spreadsheet: first use the Open Workbook command with a new name, and then use the Set Excel Cell command to set the data. Be sure to set the "Save" option when using this approach.

If the parent folder(s) and subfolders of the file do not exist, they are also created. If a document with the same name already exists, it is overwritten without additional confirmation. The File or the IF File Exists commands may be used to check existence prior to running this command.

The new spreadsheet is not automatically opened when using the "Create Workbook" command. Use the Open Workbook action to display and use the spreadsheet.

The "Row Delimiter" and "Column Delimiter" determine where new rows and columns are inside of the text. Usually, a tab or comma is used to separate columns, and each new line of text is a new row.
A basic example:



Since the input used to create the spreadsheet is raw text, embedded formatting options must be placed inside of the actual text:

    "bgcolor=red sample text here"

The tags are removed prior to creating the spreadsheet. Where possible, cell formatting options follow the standard HTML formatting names, such as "bgcolor".

Using Standard HTML Color Names for Background:   (bgcolor=)
bgcolor=red bgcolor=maroon
bgcolor=lime bgcolor=green
bgcolor=cyan
bgcolor=aqua		 bgcolor=teal
bgcolor=blue bgcolor=navy
bgcolor=magenta
bgcolor=pink
bgcolor=fuchsia		 bgcolor=yellow
bgcolor=olive bgcolor=purple
bgcolor=silver bgcolor=gray
bgcolor=black bgcolor=white
Using Standard HTML Color Names for Font:   (color=)
color=blue
bgcolor=yellow, color=red

Note: all the other colors listed above are also available
Using Border:   (border=)
border=1
border=2

Note: Other border settings are not currently supported.
Using Alignment:   (align=)
align=left
align=center
align=right

Note: Vertical alignment settings are not currently supported.
Using Column Width:   (width=)
width=200
width=50

Note: Vertical height settings are not currently supported.
Using Font:   (font=, font-size= and font-style=)
font=Verdana font-size=20
font=Times-New-Roman font-size=16
font-style=italic+bold+underlined

Note: Spaces inside of a font name should be replaced with "-" or "_"
(due to lack of any HTML syntax enforcement in clear text)



vTask is able to include formula calculations in Excel spreadsheets:

 

All of the following are valid formulas that vTask can accurately create formulas for in Excel, when using the "Create Workbook" action:

=8+9
=8 + 9.5
=A1+B2
=$A$1 + $B$2
=A1 + B2 / A1
=A1 * B2 + 100
=A1 & B2
=A1 < B2
=A1 = B2
= A1 * B2 * C3 + 123.456
=A1 * 0.05


Excel Functions (such as SUM) and expressions with parenthesis are not supported with the "Create Workbook" action. However, functions and calculations can be set with the Set Excel Cell command listed below. Set Excel Cell has no restrictions on what can be placed into a cell - even images can be inserted.

When vTask creates a spreadsheet for you, it will intelligently format the cell to fit the data. The default cell formatting options are used for Text, Numbers, Money/Currency, and Dates.
Examples:

 



  Open Workbook
 

Opens or creates a Excel workbook. A "workbook" is a collection of sheets. Before using other Excel actions such as "Get Cell", this command must be used.

*Tip:  All Excel actions require that the Open Workbook command is used first. This allows vTask to access the correct spreadsheet.

This action can be used with a filename that does not exist yet. If the XLS file does not exist, a new (blank) workbook is created. The "Save" option will immediately save the new workbook to disk after creation.

The "Hidden" option will open the workbook, but it will not be visible to the user. Use this option to update spreadsheets without displaying the Excel interface. The spreadsheet will run in the background, until the Close Workbook command is used.

To start the workbook with an initial cell or range selected, enter the appropriate names into the "Cell or Range" field. See the Select Cell action for a discussion of how to address specific Sheets, Cells, and Ranges.

The Close Workbook command should be used when you are finished with all Excel actions.

NOTE: It is not possible to step-through Excel commands. They must be executed with a single thread, from open to close.


  Save Workbook
 

Writes a workbook to disk.
This command allows you to save a spreadsheet during the middle of editing, without needing to close the workbook. You can also save a workbook by selecting the same option in the Open Workbook and Close Workbook actions.


  Close Workbook
 

Exits a workbook that was previously opened with the Open Workbook command. The "Save" option will write all changes to disk. Without "Save" chosen, the file will not be saved, and no notification will be given.

If you want to leave the spreadsheet open after the script completes, you do not need to call the Close Workbook action. However, it is important to use Close Workbook if you opened the spreadsheet in "Hidden" mode - otherwise the process will continue running, invisible and inaccessible to the user.


  Get Cell
 

Retrieves the data contents (text, number, or date) from an Excel sheet cell or group of cells. The "Workbook Name" is required because it determines which spreadsheet to use. This is important for situations where you are using multiple workbooks at the same time. The name should match the same name that was used in the Open Workbook command (an Excel .XLS file name).

This command is able to get the contents from more than one cell, also called a "Range" of cells. You address the range the same way that you would in Excel itself, such as B2:C4. See the Select Cell action for a discussion of how to address specific Sheets, Cells, and Ranges.

You can set the "Column Delimiter" to be anything you want, such as a comma, or even a longer string. If nothing is provided, a tab character will be used. This is the same as Excel itself provides, which can be verified by copying a group of cells to the clipboard.

The following data would be returned for this spreadsheet:


abc   100
def   200
ghi   300


  Set Cell
 

Places new data (text, number, or date) into an Excel sheet cell or group of cells. The "Workbook Name" is required because it determines which spreadsheet to use. This is important for situations where you are using multiple workbooks at the same time. The name should match the same name that was used in the Open Workbook command (an Excel .XLS file name).

Be sure to use the "Save" option with either Open Workbook or the Close Workbook command to ensure that the cell changes are stored.

This command is able to set the contents from more than one cell, also called a "Range" of cells. You address the range the same way that you would in Excel itself, such as "B2:C4". See the Select Cell action for a discussion of how to address specific Sheets, Cells, and Ranges.

To clear a cell (or range), use an empty value for the "Data" field (leave it blank).

The "Save Document" option allows you to make the cell change permanent. This option is provided in case you want to make multiple changes before saving the document, or allowing the user to be prompted during the save, such as when the file already exists.

To place a bitmap in a cell, use the filename as the data:




  Select Cell
 

This command places the highlight box in the location that you specify. It is a very versatile command, and is able to perform the following:
For example, to put the current selection into a single cell, use an entry such as "B2":



To put the current selection into a group of cells, use an entry such as B2:C4:



To put the current selection into a group of cells in a specific sheet, use an entry such as Sheet2!B2:C4:
(these cell/range naming conventions are the same as Excel itself uses)




  Get Cell Format
 

This is a general-purpose command that allows you to retrieve many aspects of a cell's formatting, such as the font, color, and other options.

For the example table below, the following spreadsheet is used for demonstration:

     


FormatDescriptionReturn Example

(using sample
spreadsheet above)
Data Format Returns the specified formatting for data (such as numbers). For example, "#.00" would be numeric, and "@" would be text. These are the same as the format specifications inside of Excel, as shown in the screen image below. #,##0.00
The "Custom" option shows what data formats are returned by vTask:



Is Bold 1 (TRUE) of the font is Bold, else 0 (FALSE) 1
Is Italic 1 (TRUE) of the font is Italic, else 0 (FALSE) 1
Is Underlined 1 (TRUE) of the font is Underlined, else 0 (FALSE) 1
Font Name The face name of the font Tahoma
Font Size The point size of the font 16
Font Color The HTML color of the font (RGB) #0000FF
Background Color The fill color of the cell (RGB) #FFFF99
Border Weight The thickness of the border line around the cell medium
Border Line Style The type of line of the border line around the cell dash dot
Horizontal Alignment The position of the data within the cell center
Vertical Alignment The position of the data within the cell bottom
Column Width The horizontal size of the cell, measured in pixels. NOTE: This value may not be exact, due to Excel's use of typeface points rather than pixels 120
Row Height The vertical size of the cell, measured in pixels 64
 
Total Rows Returns the count of rows in a sheet. This is the most distant vertical cell with data. The "Cell or Range" parameter is not used. 1
Total Columns Returns the count of columns in a sheet. This is the most distant horizontal cell with data. The "Cell or Range" parameter is not used. 1



  Set Cell Format
 

This is a general-purpose command that allows you to change many aspects of a cell's formatting.
You can apply formatting changes to a group of cells. See the Move to Cell action for a discussion of how to address specific Sheets, Cells, and Ranges.

FormatDescriptionSample Format TextExample
Cell Formatting Changes the data display for a cell, such as decimal places. This is the same as the "Format Cells - Number" command inside of Excel (see screenshot below)
0.0
mm/dd/yyyy
  #,##0_);[Red](#,##0) 

@   (for text)
  "#,###.0" will show: 



  "dddd, mmmm dd, yyyy" will show: 






Bold Changes font to bold 1=Bold, 0=Not Bold
Italic Changes font to italic 1=Italic, 0=Not Italic
Underline Changes font to underlined 1=Underlined, 0=Not Underlined
Font Name Changes the font typeface Same as Font selection in Excel, such as "Verdana"
Font Size Changes font point size Same as point size in Font selection, such as "18"
Font Color Changes font color.
Use standard HTML colors (RGB)		 #FF0000 = red
#808080 = gray
Background Color Changes cell color.
Use standard HTML colors (RGB)		 #f8f800 = red
#f0f0f0 = light gray
Horizontal Alignment Placement of text inside a cell left, center, right
Vertical Alignment Placement of text inside a cell top, middle, bottom
Column Width Size of a column Number of pixels for new width
Row Height Height of a row Number of pixels
Border Changes border around cell none, thin, medium, thick