ajWriteFile function
Description
The ajWriteFile function exports data from a range of cells in an Excel worksheet to a file.
Syntax
ajWriteFile(data, file_location, [write_option], [column_separator], [left_enclosure_character], [right_enclosure_character], [character_set], [number_of_retry], [interval_of_retry], [skip_empty_row], [run_condition],[run_by_function_point_only])
| Argument Name | Description |
|---|---|
| data (required) | The range of cells to be written into the file. The range of cells can span across multiple rows and columns. Each row represents a broken line and all the columns will be concatenated. |
| file_location (required) | Specify the path of the file to be written. |
| write_option (optional) | 1 indicates to overwrite the file if it already exists. 0 indicates to append the content of the range of cells selected in ‘Data’ to the file. If you do not specify anything, the default value will always be 0. |
| column_separator (optional) | Specify that character that is used to split input record into fields. In the case of a CSV file, a comma is usually used. |
| left_enclosure_character (optional) | Specify the left enclosure character. When a Column_separator is used, a field value itself can contain a column separator character. For example, when using a comma delimited format and with a field containing "Cathy, Chow" as its actual value, the field would be separated into two fields as there is a comma in. Using an enclosure character in writing and reading operations can solve this problem. If you do not specify anything, the default value will always be an empty string (“”) which means no enclosure character. |
| right_enclosure_character (optional) | Specify the right enclosure character. If you do not specify anything, the default value will always be an empty string (“”) which means no enclosure character. |
| character_set (optional) | Specify the character set used when writing the file. Some common character set codes are UTF-8 (ISO 10646 Unicode), GB2312 (Chinese Simplified), Big5 (Chinese Traditional), us-ascii. If you do not specify anything, the default value is UTF-8. |
| number_of_retry (optional) | Specify the number of retries if the file to be written is locked by other processes. If you do not specify anything, the default value is 3. |
| interval_of_retry (optional) | Specify the interval between each retry in milliseconds. If you do not specify anything, the default is 1000 which means 1 second. |
| skip_empty_row (optional) | If it equals FALSE, the empty rows will be read from the file. If it equals TRUE, the empty rows will not be read from the file. If you do not specify anything, the default value will always be FALSE. |
| run_condition (optional) | The function will run when the value is TRUE. Otherwise, it will not run. If you do not specify anything, the default value will always be TRUE. |
| run_by_function_point_only (optional) | If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Run Function Point. If it equals TRUE, the function can be executed with Run Function Point (AlchemyJ toolbar \ Run Function Point) only. If you do not specify anything, the default value will always be TRUE. |
The function will return:
1) Content type: TRUE / FALSE
2) Method: Within a cell
Example
Here are a few examples of the ajWriteFile function.
Example 1
This example demonstrates the append option. The below screenshot shows the file content before we run the function. It has one line "Hello World~".
Write_option is omitted. Hence, the default value of 0 (append) is used.
Values in the range of cells B1:E5 are appended to the end of the file. Note that since no separator was defined, cell values on the same row are concatenated.
Example 2
Let us see an example of overwriting. Similar to example 1, the contents of the file is ‘Hello World~’ before the function is run.
Write_option is set to 1 (override).
Values in the range of cells B1:E5 overwrite the previous content. Note that each value is separated by a comma and surrounded by round brackets since the comma was set as the column separator and round brackets were set as the left and right enclosure characters.