WriteExcel
File management function.
The WriteExcel function writes a cell, a row or a column in the specified Excel workbook. Microsoft Excel must be installed on the PC, but does not need to be opened. The workbook must exist but must not be opened.
Usage
Mainly used to populate columns or lines of data in an Excel file, those data can be captured on a Web site, or copied from a Windows application. As the Excel file must exist, you can use CreateExcelFile function to create it.
Syntax
WriteExcel(<workbook>,<range_descriptor>,<array$>[,<readPasswd$>][,<editPasswd$>])
or
ret=WriteExcel(<workbook>,<range_descriptor>,<array$>[,<readPasswd$>][,<editPasswd$])Parameters
<workbook>, string, name of the Excel workbook to write. <workbook> is a string which can be a constant or a variable. Long file names and UNC names are accepted, such as \\Server\c\my_directory\my_file.xls. If the path is not specified, the file is searched in the current directory.
<range_descriptor>, string. If only one sheet exists in the workbook, only the range of cells must be specified, such as A4:F4 for a row or A2:A12 for a column. If multiple sheets exist in the workbook, the required syntax is "sheet_name!range_of_cells"; for example, TOOLS!A9:F9
Other range examples: mycell1 (a named cell), A1, A3:B3, tools!$b$2:$b$4
<array$>, array of strings. The array must be declared at the beginning of the script using Dim. The contents of the non-empty cells in <array> are written to <range_descriptor>, beginning with cell 0. If the array is larger than the range, only the number of cells which fit in the range are written.
<readPasswd$>, optional string parameter; password for reading the <workbook> if needed.<editPasswd$>, optional string parameter; password for editing the <workbook> if needed.
Return value
Ret, numeric return code. When the write is successful, the function returns the number of cells written, otherwise use this return code for Error Handling. The return code is -1 if an error is reported by Excel or the range is invalid. The return code is -3 if the Excel file is opened (WriteExcel cannot write in an opened Excel file).
See also
How to read all the lines of an Excel worksheet until the cell in the first column is empty
Example
dim array$(20)
'fill array$ and then
ret=WriteExcel("C:\My documents\myexcel_file.xls","TOOLS!A9:F9",array$())
Example code
The script below reads all the lines from an Excel file from A column to C column and writes them in another Excel file.
dim arrayline$(10)
fileexcel$="c:\example1.xls"
filetowritein$="c:\test.xls"
j=1
repeat
' The complex string "sheet1!"+"a"+str$(j)+":c"+str$(j) means if j=1
' "Sheet1!a1:c1"
readexcel(fileexcel$,"sheet1!"+"a"+str$(j)+":c"+str$(j),arrayline$())WriteExcel(filetowritein$,"sheet1!"+"a"+str$(j)+":c"+str$(j),arrayline$())
j=j+1
until arrayline$(0)=""