Excel, useful functions

Excel useful functions

The ReadExcel and WriteExcel functions can be used for reading a full line, writing some cells in a column, ...

The functions listed below can be used for a structured script when complex read/write to Excel is involved.
Please remember that WinTask uses only one-dimension arrays and that arrays must be declared at the beginning of the script using Dim statement.

You can find the corresponding script file under Script_Examples, sub-directory of scripts.

'GLOBAL VARIABLES FOR ALL THOSE FUNCTIONS ARE :

'excel_file$ for the name of the Excel file, the Excel file must exist but must not be opened.

'workbook$ for the name of the workbook.

'The array tab_data$ must be declared at the beginning of the script : Dim tab_data$(1000).

Dim tab_data$(1000)

'The goal of this functions is to write in the Excel file excel_file$ the value param$

'at cell specified by column$/line$

'Entries are param$,column$,line$

'---------------------------------------------------------------------------------------------

function write_excel_data$(param$,column$,line$)

local res, buffer

buffer=#IgnoreErrors

#IgnoreErrors=1

tab_data$(0)=param$

res=writeexcel(excel_file$,workbook$+"!"+column$+line$,tab_data$())

pause 5

if res<>1 then

msgbox("Impossible to write cell : \n\line : "+line$+"\n\column : "+column$,16,"Error writing Excel file")

stop

endif

#IgnoreErrors=buffer

endfunction

'*******************************************************************************************

'The goal of this functions is to read in the Excel file excel_file$ the value in the

'cell specified by column$/line$. The result is in retrieve_excel_data$

'---------------------------------------------------------------------------------------------

function retrieve_excel_data$(column$,line$)

local res, buffer

buffer=#IgnoreErrors

#IgnoreErrors=1

res=readexcel(excel_file$,workbook$+"!"+column$+line$,tab_data$())

if res<>1 then

msgbox("Impossible to read cell : \n\line : "+line$+"\n\column : "+column$,16,"Error reading Excel file")

stop

endif

#IgnoreErrors=buffer

retrieve_excel_data$=tab_data$(0)

endfunction

'*******************************************************************************************

'The goal of this functions is to read in the Excel file excel_file$ all the cells in one line

'line$ from start_column$ to end_column$ ; the retrieved values are in array tab_data$,

'the function returns the number of cells retrieved.

'---------------------------------------------------------------------------------------------

Function retrieve_excel_line_data(line$,start_column$,end_column$)

local res, buffer

buffer=#IgnoreErrors

#IgnoreErrors=1

res=readexcel(excel_file$,workbook$+"!"+start_column$+line$+":"+end_column$+line$,tab_data$())

if res < 0 then

msgbox("Impossible to read cell : \n\line : "+line$+"\n\column : "+start_column$,16,"Error reading Excel file")

stop

endif

#IgnoreErrors=buffer

retrieve_excel_line_data=res

endfunction

'*******************************************************************************************

'The goal of this functions is to read in the Excel file excel_file$ all the cells in one column

'column$ from start_line$ to end_line$ ; the retrieved values are in array tab_data$,

'the function returns the number of cells retrieved.

'---------------------------------------------------------------------------------------------

Function retrieve_excel_column_data(column$,start_line$,end_line$)

local res, buffer

buffer=#IgnoreErrors

#IgnoreErrors=1

res=readexcel(excel_file$,workbook$+"!"+column$+start_line$+":"+column$+end_line$,tab_data$())

if res < 0 then

msgbox("Impossible to read cell : \n\column : "+column$+"\n\line : "+start_line$,16,"Error reading Excel file")

stop

endif

#IgnoreErrors=buffer

retrieve_excel_column_data=res

endfunction

'*******************************************************************************************

'The goal of this functions is to write in the Excel file excel_file$, at line line$ the data stored in tab_data$

'starting at start_column$ and ending at end_column$ ;

'the function returns the number of cells written.

'---------------------------------------------------------------------------------------------

Function write_excel_line_data(line$,start_column$,end_column$)

local res, buffer

buffer=#IgnoreErrors

#IgnoreErrors=1

res=writeexcel(excel_file$,workbook$+"!"+start_column$+line$+":"+end_column$+line$,tab_data$())

if res < 0 then

msgbox("Impossible to write cell : \n\line : "+line$+"\n\column : "+start_column$,16,"Error writing Excel file")

stop

endif

#IgnoreErrors=buffer

write_excel_line_data=res

endfunction

'*******************************************************************************************

'The goal of this functions is to write in the Excel file excel_file$, at column column$ the data stored in tab_data$

'starting at start_line$ and ending at end_line$ ;

'the function returns the number of cells written.

'---------------------------------------------------------------------------------------------

Function write_excel_column_data(column$,start_line$,end_line$)

local res, buffer

buffer=#IgnoreErrors

#IgnoreErrors=1

res=writeexcel(excel_file$,workbook$+"!"+column$+start_line$+":"+column$+end_line$,tab_data$())

if res < 0 then

msgbox("Impossible to write cell : \n\column : "+column$+"\n\line : "+start_line$,16,"Error writing Excel file")

stop

endif

#IgnoreErrors=buffer

write_excel_column_data=res

endfunction

'*******************************************************************************************

'The goal of this functions is to display the nb values in tab_data$

'---------------------------------------------------------------------------------------------

sub display_data(nb)

local mes$, i

i=0

mes$=""

repeat

mes$=mes$+"\n\"+tab_data$(i)

i=i+1

until i=nb

msgbox(mes$)

endsub

'*******************************************************************************************

'The goal of this functions is to give the number of cells in one column column$, starting at line

'number start_line for a maximum of cells in maxline.

'---------------------------------------------------------------------------------------------

function nb_line_excel(excel_file$,column$,start_line,Maxline)

local ret, i , sortie

ret=0

sortie=0

ret=ReadExcel(excel_file$,column$+str$(start_line)+":"+column$+str$(maxline),tab_data$())

i=0

repeat

if rtrim$(ltrim$(tab_data$(i)))<>"" then

i=i+1

else

sortie=1

endif

until sortie=1 or i=maxline

nb_line_excel=i

endfunction

'EXAMPLES showing how to use those functions.

excel_file$="C:\wttest\my_worksheet.xlsx"

workbook$="Sheet1"

a$=retrieve_excel_data$("C","14")

msgbox(a$)

res=retrieve_excel_line_data("14","C","H")

display_data(res)

res=retrieve_excel_column_data("C","1","15")

display_data(res)

write_excel_data$("25","C","17")