autofill in excel ... down Topic is solved

[jerryrig]

in the usual case if we want nos from 1 to 10

in cell a1 type 1
a2 type 2

select a1 and a2

place mouse to see fill handle ..and pull it down ....
is there an ahk replacement

[boiler]

Code: Select all

XL := ComObjActive("Excel.Application")
SourceRange := XL.Worksheets("Sheet1").Range("A1:A2")
SourceRange.AutoFill(XL.Worksheets("Sheet1").Range("A1:A10"))

Or:

Code: Select all

XL := ComObjActive("Excel.Application")
SourceRange := XL.Worksheets("Sheet1").Range("A1:A2")
DestinationRange := XL.Worksheets("Sheet1").Range("A1:A10")
SourceRange.AutoFill(DestinationRange)

[jerryrig]

wow thats cool ..... btw can i just move mouse to show the fill handle ..i dont want to drag .......
ie just move mouse to bottom right

[boiler]

You could use ImageSearch for the fill handle and move it to the found location, however, the fill handle will look different when it's on a cell with a different border or shading. There might be a way to determine the x,y location and the width and height of the active cell, but I don't know how to do it.

[jerryrig]

i thought there might be some unknown COMstuffs to move mouse .....

[boiler]

i thought there might be some unknown COMstuffs to move mouse .....

Well, there is for determining the width and the height of the cell, and the position of the cell is captured in the built-in AHK variables A_CaretX and A_CaretY, so we can do this:

Code: Select all

XL := ComObjActive("Excel.Application")
SourceRange := XL.Worksheets("Sheet1").Range("A1:A2")
DestinationRange := XL.Worksheets("Sheet1").Range("A1:A10")
SourceRange.AutoFill(DestinationRange)
XL.Range("A10").Select

Width := XL.Worksheets("Sheet1").Columns("A").Width * 1.33
Height := XL.WorkSheets("Sheet"1).Rows("1").Height * 1.33
WinActivate, ahk_exe excel.exe
MouseMove, A_CaretX + Width, A_CaretY + Height, 0

[jerryrig]

Code: Select all

#SingleInstance,Force
0::
XL := ComObjActive("Excel.Application")
Width := XL.Worksheets("Sheet1").Columns("A").Width * 1.33
Height := XL.WorkSheets("Sheet"1).Rows("1").Height * 1.33
WinActivate, ahk_exe excel.exe
MouseMove, A_CaretX + Width, A_CaretY + Height, 0
return

generalised code .....thanks man