Home > Enhancing Scripts > Working with Excel

Working with 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. ExecExcelMacro function executes an Excel macro without opening the Excel file. The paragraphs below give sample scripts for the most common actions you have to do in Excel.

Read one column of an Excel worksheet

'The script reads the B column and populates an array with the content of the first 100 cells.
'Declare the array used for reading the first 100 cells
Dim column$(100)
'Use a variable for speficying the Excel file to read, it is supposed in this example that data.xlsx file is in c:\wttest folder and has some data in B column.
fileexcel$="C:\wttest\data.xlsx"
'Read the first 100 cells of the B column and put them in the array column$()
ReadExcel(fileexcel$,"B1:B100",column$())
'Just for a check, display the 5 first elements of the column$() array. Index starts at 0
i=0
repeat
    msgbox(column$(i))
    i=i+1
until i=5



Read one line of an Excel worksheet

'The script reads the ith line for cells from Bi until Gi, so for example if i=6, from B6 to G6
'Declare the array used for reading the line
Dim line$(10)
'Use a variable for speficying the Excel file to read, it is supposed in this example that data.xlsx file is in c:\wttest folder and has some data in B6 to G6 cells.
fileexcel$="C:\wttest\data.xlsx"
'Read from B6 to G6
ReadExcel(fileexcel$,"B6:G6",line$())
'Read from Bi to Gi, note the use of str$ function to convert the integer i into a string, and + for concatenation
i=6
ReadExcel(fileexcel$,"B"+str$(i)+":G"+str$(i),line$())
'Just for a check, display the 6 elements of the line$() array. Index starts at 0
i=0
repeat
    msgbox(line$(i))
    i=i+1
until i=6



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

'Declare the array used for reading one line.
dim line$(10)
'Use a variable for specifying the Excel file to read.
fileexcel$="C:\wttest\data.xlsx"

i=1
repeat
readexcel(fileexcel$,"A"+str$(i)+":H"+str$(i),line$())
'Just for a check, display the first element of each line
msgbox(line$(0))
'Here add the lines where the data in line$ are used, for example you can fill a form with line$(0), line$(1) and so on.
i=i+1
until line$(0)=""




If for any reason, WinTask x64 Excel functions does not cover your automation needs, and still want to automate Excel interface, use the UI Automation framework for selecting a menu option (available only in the 64 bit automation software). We give below some Sub for different automation needs.

Go to a cell and paste the clipboard content

Function goto_cell(cell$)
'This function puts the cursor on the cell specified in cell$
'Click Find & Select in Excel ribbon bar
ClickUIA("[APP='EXCEL.EXE',CLASS='NetUIHWND',IDX=2][NAME='Editing',ROLE='tool bar'][NAME='Find & Select',ROLE='split button']")
'The Sub-menu opens, click Go To option
ClickUIA("[APP='EXCEL.EXE',CLASS='NetUIHWND'][NAME='Go To...',ROLE='menu item']")
'The Go To dialog box is displayed, type the content of cell$
UseWindow("EXCEL.EXE|EDTBX|Go To|1",1)
SendKeys(cell$)
pause 10 ticks
Sendkeys("<Enter>", Noactivate)
EndFunction

'EXAMPLE: launch Excel and open c:\wttest\data.xlsx, then go to cell B7 and paste the content of Clipboard.
'This Shell line is generated automatically by using Start/Recording after launching a program
Shell(Chr$(34)+"C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE"+Chr$(34)+" C:\wttest\data.xlsx",1)
'Put a data into Clipboard
SetClipboard("new value")
'Call the goto_cell function to go to cell B7
goto_cell("B7")
'Paste the value which is stored in Clipboard
SendKeys("<Ctrl v>",Noactivate)

Save an Excel file under a new filename using current month and day

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

'Click the Office button using its UIA descriptor
ClickUIA("[APP='EXCEL.EXE',CLASS='NetUIHWND',IDX=2][NAME='Office Button',ROLE='button']")
'Click the Save As sub-menu option
ClickUIA("[APP='EXCEL.EXE',CLASS='NetUIHWND',IDX=2][NAME='Save As',ROLE='split button']")
'Click Excel workbook
ClickUIA("[APP='EXCEL.EXE',CLASS='NetUIHWND'][NAME='Excel Workbook',ROLE='menu item']")
'In the Save As window, type the new filename adding current month and current day.
UseWindow("EXCEL.EXE|Edit|Save As|1")
'Add to the Excel filename (including the full path) the current month and day
SendKeys("C:\wttest\data.xlsx"+month$()+day$())
SendKeys("<Enter>")

Insert a SUM (or any other) function in a specific cell

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 goto_cell other function which is listed above.
Usewindow(top$())
goto_cell(result_cell$)
usewindow("EXCEL.EXE|EXCEL<|Microsoft Excel - |1")
sendkeys("=SUM("+start_cell$+":"+end_cell$+")", NoActivate)
pause 15 ticks
sendkeys("<Enter>")
endfunction

add_column_cell("B5","B27","E5"))


See also

Handling Events
Working with Databases
Naming Objects, Spy Tool
Improving Object Recognition by using UI Automation Technology
Adding Dialog Boxes