Task Office - Excel - Set Cell
The Excel - Set cell Task writes a value, or a table of values, into existing Excel workbooks, starting at a cell you choose. It is similar to the Excel - Create Task, but updates workbooks instead of creating new ones.
The input is a value or a Variable. Each line of the input becomes a row and each field a cell, starting at the start cell and going right and down. For example, with the start cell at row 2, column 2, a field separator of "Comma" and this input:
col1row1,col2row1
col1row2,col2row2
the values are written to the cells from row 2, column 2 to row 3, column 3 ("B2" to "C3").
The Main settings, Flow, On error, Conditions and TimeOut tabs are the same for every Task; see Job - Tasks.
The Task settings are on the Excel - Set cell tab, which has the sub tabs Source, File filter, Column format, Target and Output.
Source tab

-
Input value: The value or Variable to write
Its format must match the settings below so that rows and fields are split correctly. Values are written as text unless the Column format tab sets another type.
Format
-
Field separator: The character between fields, "Tab" by default
-
Text qualifier: The character around field values, if any; none by default
A separator inside a qualified value still splits the field.
-
Line break: The character between rows, "CrLf" by default
With an Excel 97-2003 workbook and "CrLf", an empty row is written between the rows of the input. Use "Lf" or "Cr" with input that matches, or a ".xlsx" workbook.
File filter tab

The File filter tab selects the workbooks to update; see Task - File filter. The Credential on its Location tab is used to read the workbooks.
When no file matches and the Include file mask names one file without wildcards, the Task creates that workbook in the filter folder.
When no file matches a file mask with wildcards, the Task writes nothing, and the Task reports success.
Column format tab

The list sets the format of the written columns, in order: the first entry applies to the first field of each row, and so on. Click Add, Edit or Delete to change the list.
The Add/Edit column window has these settings:
-
Type: How the field is written: "Text", "Number", "Date" or "Formula"
A "Number" or "Date" field that cannot be converted is written as text. A "Formula" field gets a leading "=" if it has none.
-
Format: The Excel number format of the cells, "@" (text) by default
For the format codes, see http://www.ozgrid.com/Excel/CustomFormats.htm.
-
Horizontal alignment: The alignment of the cells
-
Width and Auto fit: The column width
Target tab

Worksheet
-
Use first worksheet: Writes to the first worksheet
-
Use specific worksheet: Writes to the worksheet with the name you enter; it can contain Variables
If a ".xlsx" workbook has no worksheet with that name, the Task adds it.
-
Clear cells before updating: Clears the whole worksheet before the values are written, not only the target cells
Clear cells before updating removes every value on the worksheet. With a ".xlsx" workbook it also removes the cell formatting.
Cell coordinates
- Cell by position: The start cell by Row and Column, starting at "1"
- Cell by reference (i.e. A3): The start cell by Cell reference, for example "A3"
The coordinates can contain Variables.
Output tab

Save to..
- Same file: The default; saves the updated workbook over the original file
- Same folder but use file mask: Saves to the folder of the File filter tab with the name from File mask
- Different folder and use file mask: Saves to Folder with the name from File mask
- Folder: The destination folder
- File mask: The name of the saved file, "*.xlsx" by default
Folder and File mask can contain Variables.
With Same file, the default, each workbook is saved over the original file. An existing file with the destination name is also replaced without warning.
-
Credentials: The Credential used to save ".xlsx" workbooks; see Credentials
An Excel 97-2003 destination file is created with the Credential of the File filter tab.
Select a Credential in the combo box, or click the Settings icon to add or edit Credentials.
Password protection
-
Use password when accessing file: Opens protected workbooks with the password you enter, and saves them with the same password
-
Remove password when saving: With Use password when accessing file, saves the workbooks without a password
The Task does not clear Use password when accessing file after it removes the password.
-
Only remove password: Writes no values, so only the password settings apply; the Source tab is not available
To remove a password, also select Use password when accessing file and Remove password when saving.
Output and errors
The output of the Task is the path of each workbook it saved.
With an Excel 97-2003 workbook (".xls"), errors while the workbook is opened, updated or saved are not reported: the workbook is left unchanged or, with Same file, can be left empty, and the Task reports success. Check the workbook after the Task runs, or use a ".xlsx" workbook, where such errors stop the Task.