ControlClick with Excel

[VDIwarrior]

Hi AutoIT gurus,

I'm trying to use autoIT on Microsoft Excel. I'd like to do this without using COM. What I'm trying to achieve is, key in 10 numbers in the first column (A1 to A10). Then I'd like to highlight all 10 by a mouseclick on the cell named "A". Then I'd like to sort them.

Here's the snippet I've written to highlight the 10 numbers

Run("C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE")

WinWaitActive("Microsoft Excel - Book1")

Send("1{ENTER}2{ENTER}3{ENTER}4{ENTER}5{ENTER}6{ENTER}7{ENTER}8{ENTER}9{ENTER}10{ENTER}")

ControlFocus("Microsoft Excel - Book1", "", "[CLASS:XLDESK; INSTANCE:1]")

ControlClick("Microsoft Excel - Book1", "", "[CLASS:XLDESK; INSTANCE:1]", "left", 1, 64, 15)

'ControlClick' just does not work. I used the autoIT window info tool to get information about the control I want to use (XLDESK1 is the main spreadsheet control). I'm particular on using ControlClick and not MouseClick because I always want to work within the XLDESK1 control, because I dont know where the window might be drawn to get the absolute co-ordinates. I got the co-ordinates (64,15) from the window info tool in the "ControlClick Coords" field.

OTOH, If i use "right" instead of "left" in ControlClick and skip the rest of the arguments, then as expected a right click happens on the main spreadsheet control.

Another problem I've seen with ControlClick and M$ word is that when I send ControlClick on toolbar instance 3, it goes to toolbar instance 1 !! (i.e. when I try to click on "Open" button, the "File" menu item is clicked)

Please chime in with your valuable solutions.

[PsaltyDS]

Not tested because I use OO.o, and don't have Excel installed here:

#include <"ExcelCOM_UDF.au3">
#include <"array.au3>

$sXLS = @ScriptDir & "\Test.xls"

$oXLS = _ExcelBookNew()
For $n = 1 To 10
    _ExcelWriteCell($oXLS, Random(1, 100, 1), "A" & $n)
Next

$avXLS = _ExcelReadArray($oXLS, 1, 1, 10, 1)
_ArrayDisplay($avXLS, "Debug: Before sort")

$avXLS = _ExcelSort($oXLS, "A", 1, 1, 10, 1, 1)
_ArrayDisplay($avXLS, "Debug: After sort")

_ExcelBookSaveAs($oXLS, $sXLS)

[VDIwarrior]

Hi PsaltyDS, thanks a lot for your reply. Sorry for my delay in responding.

The script didnt work for me, but that definitely doesnt mean it is wrong. I dont have the libraries you have included (ExcelCOM_UDF.au3 and array.au3). It seems like you guys have standardized on automating Office (Excel, word, powerpoint) and use libraries for it. Where can I find the most recent versions of these libraries. I'm very interested in word, excel, powerpoint.

thanks again.

[PsaltyDS]

Hi PsaltyDS, thanks a lot for your reply. Sorry for my delay in responding.

The script didnt work for me, but that definitely doesnt mean it is wrong. I dont have the libraries you have included (ExcelCOM_UDF.au3 and array.au3). It seems like you guys have standardized on automating Office (Excel, word, powerpoint) and use libraries for it. Where can I find the most recent versions of these libraries. I'm very interested in word, excel, powerpoint.

thanks again.

The Array.au3 UDF comes with AutoIt.

Locodarwin's ExcelCOM_UDF.au3 is posted in the Example Scripts forum.

Using the forum's search (top right corner of the forum window) to find it is left as an exercise for the student...

[VDIwarrior]

I tried your script and it gives an error at the following line

$avXLS = _ExcelSort($oXLS, "A", 1, 1, 10, 1, 1)

The error is as follows

C:\Program Files\AutoIt3\Include\ExcelCOM_UDF.au3 (984) : ==> The requested action with this object has failed.:

$oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Sort ($oExcel.Range($sKey), $iDirection)

$oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Sort ($oExcel.Range($sKey)^ ERROR

>Exit code: 1 Time: 3.759

I had another question. The purpose of the script I am trying to write is to simulate actual user behavior as faithfully as possible, so thats the reason I am particular about using mouse clicks and not COM. The desktop environment that I'm testing out is not actually a typical one and the overall script execution time itself might vary based on how efficient things like mouse movements, screen refresh, etc are. Using COM might significantly alter the behavior and might not necessarily exercise what I actually want.

Is there any way to make controlclick work ? I saw a number of threads related to problems with controlclick, and the most common solutions posted were to use controlfocus, or use mouseClick with absolute co-ordinates. Controlfocus didnt work, and using absolute co-ords doesnt sound too good. Or if I've succeeded in explaining my requirements, please suggest what else can be done other than COM.

thanks a lot.

[PsaltyDS]

I tried your script and it gives an error at the following line

$avXLS = _ExcelSort($oXLS, "A", 1, 1, 10, 1, 1)

The error is as follows

C:\Program Files\AutoIt3\Include\ExcelCOM_UDF.au3 (984) : ==> The requested action with this object has failed.:

$oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Sort ($oExcel.Range($sKey), $iDirection)

$oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Sort ($oExcel.Range($sKey)^ ERROR

>Exit code: 1 Time: 3.759

Bad parameter on my part. That "A" is supposed to mean sort on column A, but it still has to be a valid range value, so it should be:

$avXLS = _ExcelSort($oXLS, "A1", 1, 1, 10, 1, 1)

I had another question. The purpose of the script I am trying to write is to simulate actual user behavior as faithfully as possible, so thats the reason I am particular about using mouse clicks and not COM. The desktop environment that I'm testing out is not actually a typical one and the overall script execution time itself might vary based on how efficient things like mouse movements, screen refresh, etc are. Using COM might significantly alter the behavior and might not necessarily exercise what I actually want.

Is there any way to make controlclick work ? I saw a number of threads related to problems with controlclick, and the most common solutions posted were to use controlfocus, or use mouseClick with absolute co-ordinates. Controlfocus didnt work, and using absolute co-ords doesnt sound too good. Or if I've succeeded in explaining my requirements, please suggest what else can be done other than COM.

thanks a lot.

I have no idea what all would be required to do your functions with ControlClick in Excel. The cells in the visible sheet are NOT controls, so I don't see how you could click on a particular one. If the graphical environment and sheets you are working with were absolutely stable, you might get away with just MouseClick using X/Y coordinates.

...awfully painful that way.