Automation Action: Update Excel File

Update a Microsoft Excel Spreadsheet file.

Enter or select the Excel File Name to update. Optionally enter the Worksheet Name to update. If no worksheet is specified then the first worksheet in the Excel file will be updated.

ThinkAutomation will create the file if it does not already exist. Excel does not need to be installed on the ThinkAutomation computer for this Action to work.

Select the Operation:

Add New Row

Add a new row to the worksheet.

If the Automatically Add A New Row Using Extracted Fields option is selected, then a new row will be automatically added with a column for each Extract Field action.

The spreadsheet will be in the following format:

ExtractedField1	ExtractedField2	ExtractedField3
Value	Value	Value

If the worksheet contains no rows then a header line will be created with each extracted field name.

The data line will be added to the existing data lines with each extracted field value.

If Automatically Add A New Row Using Extracted Fields option is not selected then you must specify the Value and Header for each column number. Specify each Column Number that you want to add (starting from column 1). Then specify its Value and Header. The value can be a fixed value or %variable% replacement (or combination). You can skip columns (eg: Add columns 1,3 & 5). The Header will only be used if the worksheet contains no existing rows.

Update Specific Cells

Select the Update Specific Cells option to update specific cells within a worksheet. You can then specify specific cell references (eg: A1, B4, E23 etc) and the value to assign each cell. The value can be a fixed value or %variable% replacement (or combination).

Insert CSV Data

This option enables you to insert CSV data into a worksheet. In the Insert A Cell Reference enter the cell reference where the inserted rows can columns should start. If this entry is blank then the data will be inserted starting at the last row used in the existing worksheet (or at the first row for a blank spreadsheet).

If the Shift Rows Below Down option is enabled then any rows below the specified Insert At Cell Reference will be shifted down to make room for the inserted rows (does not apply if no Insert At Cell Reference specified or if the spreadsheet is blank).

Specify your CSV data in the CSV Data Or File Path entry. This can be a %variable% replacement containing CSV data or a file path. If a file path is used then the CSV data is read from the file.

Enable the Has Column Headers option if the CSV data contains column headers.

Enable the Insert Headers option if you want the CSV column headers to be inserted in the spreadsheet.

If you simply want to convert a CSV file into an Excel Document you can use the Convert Document action.

Recalculate

Enable the Recalculate option if you want ThinkAutomation to recalculate all formulas in the Excel file after any updates. Normally you would not need to enable this option (since Excel itself will recalculate when the file is opened). However, if you make use of the Excel file in subsequent actions and any updated cells are used in formulas then you should enable this option to ensure the Excel file is fully updated before its saved.

Password

Enter a Password if you are updating an existing Excel file that is password protected. If you are creating a new Excel File then it will be saved with the password (optional). Note: Updating a password protected Excel file is slower.