UpdateExcelValue

×V4.5Ø

Syntax -

UpdateExcelValue <Value> <Path to Spreadsheet> <Worksheet> <Row> <Column> <MaintainCellType>

       Where:

       <Value> is a literal string or a variable that contains a literal string

       <Path to Spreadsheet> is a literal string or a variable that contains a literal string

       <Worksheet> is a literal string or a variable that contains a literal string

       <Row> is a number or a variable containing a numeric value

       <Column> is a number or a variable containing a numeric value

       <MaintainCellType> is a keyword

Description:

This command is designed to update a single cell in an Excel spreadsheet.

<Value> contains the information to be written to the desired cell.

<Path to Spreadsheet> contains the path and name of the spreadsheet that will be updated.

<Worksheet> contains the sheet name where the cell will be updated. If the sheet doesn't exist, EMUE will create it.

Since the name of the worksheet is used in the query in place of a table name, the worksheet name must be known, or referenced based on its position.  For example, the first worksheet can be referenced with *Worksheet1 and the second sheet can be referenced by *Worksheet2.

<Row> corresponds to the row number of the cell the user wants to update.

<Column> corresponds to the column number of the cell the user wants to update.

<MaintainCellType> tells EMUE whether or not to keep the existing format for the desired cell.

When the optional parameter <MaintainCellType> is used,  EMUE will convert string values to the data type that is specified by Excel's formatting for that cell. EMUE does the following in order before each cell is written:

1) Checks target cell to see if it is a percentage, then checks data to see if last character is a %. If the last character is a % then the rest of the data is numeric it formats data as a number and divides by 100.

2) Checks to see if the data has a leading zeros. If the data is numeric it is formatted as an integer and preserves the leading zeros.

3) Checks to see if the data is numeric then changes it to a decimal.

4) Checks to see if data is a date then changes it to a date.

5) All other data remains a string.

For example, if a value of 10.5 is being exported to a cell that has type currency, then the value will be converted to $10.50.

Sample 1:

       This command updates the cell at row 2 and column 1 on the "Data" worksheet with the value "Paid".

UpdateExcelValue "Paid" "C:\EMUE\ChargeEntry\Charges.xlsx" "Data" 3 1

Sample 2:

       This command updates the cell at row 1 and column 10 with the value stored in the variable Amount. It uses the sheet moniker *Worksheet1 & MaintainCellType so the cell format is preserved in the target worksheet.

UpdateExcelValue Amount "C:\EMUE\ChargeEntry\Charges.xlsx" "*Worksheet1" 1 10 MaintainCellType