Excel

Microsoft Excel is the application most commonly used for storing data. The contents of cells in Excel can be set and retrieved directly, without the use of SendKeys function and/or Clipboard functions. Data in Excel columns or lines can be retrieved directly in arrays for easier manipulation.
ReadExcel and WriteExcel functions run in the background without the application being visible.

We give below two examples:

Read all the lines of an Excel worksheet until the cell in the first column is empty.

dim x$(10)
'Excel 2007, the file extension is xlsx. For Excel versions prior 2007, the extension is xls.
fileexcel$="C:\wttest\my_worksheet.xlsx"

j=1

repeat

readexcel(fileexcel$,"a"+str$(j)+":h"+str$(j),x$())
msgbox(x$(0))

j=j+1

until x$(0)=""

Read the first column and the second column of an Excel file which has 9 lines, and then loop for processing the read data.

'Declare the arrays for column A and column B

Dim columna$(100)

Dim columnb$(100)

 

 

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

 

'Fill columna$ array

ReadExcel(fileexcel$,"A1:A9",columna$())

'fill columnb$ array

ReadExcel(fileexcel$,"B1:B9",columnb$())

 

'a loop example

i=0

repeat

msgbox(columnb$(i))

i=i+1

until i=9

 

If for any reason, WinTask Excel functions does not cover your automation needs, and still want to automate Excel interface, use shortcuts for selecting a menu option. Two examples :

'File/Save menu

'Command line for launching Excel and loading c:\wttest\my_worksheet.xlsx
'This Shell line is generated automatically by using Start/Recording after launching a program
Shell(Chr$(34)+"C:\Program Files\Microsoft Office\Office12\EXCEL.EXE"+Chr$(34)+" C:\wttest\my_worksheet.xlsx",1)

UseWindow("EXCEL.EXE|EXCEL7|")
SendKeys("<Alt F>")
pause 10 ticks
SendKeys("S",Noactivate)


'File/Save as menu and a dynamic new Excel filename is given
'Command line for launching Excel and loading c:\wttest\my_worksheet.xlsx
'This Shell line is generated automatically by using Start/Recording after launching a program
Shell(Chr$(34)+"C:\Program Files\Microsoft Office\Office12\EXCEL.EXE"+Chr$(34)+" C:\wttest\my_worksheet.xlsx",1)

'UseWindow("EXCEL.EXE|EXCEL7|")
SendKeys("<Alt F>")
pause 10 ticks
SendKeys("a",Noactivate)
UseWindow("EXCEL.EXE|Edit|Save As|1")
'We add to the Excel filename the current month and day
SendKeys("C:\wttest\my_worksheet.xlsx"+month$()+day$())
SendKeys("<Enter>")

 

We give below some functions that you can use in an Excel worksheet automation project:

Function select_cell(cell$)

'This function puts the cursor on the cell specified in cell$
'The UseWindow with top$() parameter tells to use the window which is on top. If the Excel Window
'is not on top, you can replace top$() by the Excel window name
Usewindow(top$())
'Go to shortcut for selecting directly the Edit menu Go to option
sendkeys("<Ctrl g>",NoActivate)
UseWindow("EXCEL.EXE|EDTBX|Go To|1",1)
'In the Go To dialog box, we type the content of cell$
SendKeys(cell$)
pause 10 ticks
Sendkeys("<Enter>", Noactivate)
EndFunction

Function select_rangeofcells(cell_range$)
'This function selects the range of cells specified in cell_range$
'The UseWindow with top$() parameter tells to use the window which is on top. If the Excel Window
'is not on top, you can replace top$() by the Excel window name
Usewindow(top$())
'Go to shortcut
sendkeys("<Ctrl g>",NoActivate)

UseWindow("EXCEL.EXE|EDTBX|Go To|1",1)

SendKeys(cell_range$)

pause 10 ticks

Sendkeys("<Enter>", Noactivate)

Endfunction

 

Function add_column_cell(start_cell$, end_cell$, result_cell$)
'This function adds from start_cell$ to end_cell$ and writes the SUM formula at result_cell$
'The UseWindow with top$() parameter tells to use the window which is on top. If the Excel Window
'is not on top, you can replace top$() by the Excel window name.
'The function calls Select_cell other function which is listed above.
Usewindow(top$())

select_cell(result_cell$)

usewindow("EXCEL.EXE|EXCEL<|Microsoft Excel - |1")

sendkeys("=SUM("+start_cell$+":"+end_cell$+")", NoActivate)

pause 15 ticks

sendkeys("<Enter>")

endfunction


'Call examples, the Pause lines are added so you can see the results!
select_cell("B5")
Pause 3 secs
select_rangeofcells("B5:D27")
Pause 3 secs
add_column_cell("B5","B27","E5"))

 

 

.

See also

Excel, useful functions