Increment certain cell references by certain numbers in COM Excel?

Get help with using AutoHotkey and its commands and hotkeys
danotto94
Posts: 14
Joined: 11 Aug 2015, 10:32

Increment certain cell references by certain numbers in COM Excel?

15 Sep 2015, 10:35

E.g. if there are cells like C1, D1, etc. and underneath are C2, D2, etc., and I want to change all their numbers to one or two higher, what's the COM method?
User avatar
TLM
Posts: 1590
Joined: 01 Oct 2013, 07:52
GitHub: TLMcode
Contact:

Re: Increment certain cell references by certain numbers in COM Excel?

16 Sep 2015, 02:23

While using a formula via COM would probably be better as you would get true range support etc,
here's an example of increasing the values using AHk
Run with Excel open and a starting value at C1 and D1

Code: Select all

xlObj := ComExcelConnect( "ahk_class XLMAIN" ).Application

; IncreaseValue( [ ExcelObject, StartRange, RowsDown, IncrementBy ] ) 
IncreaseValue( xlObj, "C1:D1", 3, 1 ) 
ExitApp

IncreaseValue( xlObj, TopRange, Rows, Incr )
{
    Loop % Rows
        For i, Cell in ( StrSplit( TopRange, ":" ), Row := A_Index )
            xlObj.Range( ( Col := SubStr(Cell,1,1) ) . Row+1 ).value := ( xlObj.Range( Col . Row ).value+Incr )
}
 
ComExcelConnect( WinTitle )
{
    objID    := "-16", objID &= 0xFFFFFFFF
    refID    := -VarSetCapacity( iid, 16 )
    iid_type := NumPut( 0x46000000000000C0
             ,  NumPut( 0x20400, iid, "Int64" )
             ,  "Int64")
 
    ControlGet, hwnd, hwnd, , EXCEL71, % WinTitle
    DllCall( "oleacc\AccessibleObjectFromWindow", ptr, hwnd, uInt, objID, ptr, refID+iid_type, "ptr*", pObj )
 
    return ComObjEnwrap( 9, pObj )
}
output:
Image

You can also decrease the value by going IncreaseValue( xlObj, "C1:D1", 3, -1 )
output:
Image

Another example using just one Column IncreaseValue( xlObj, "C1", 5, 1 )
output:
Image

htms

Return to “Ask For Help”

Who is online

Users browsing this forum: Bing [Bot], JoeWinograd, manehscripts, mikeyww, nu10e8368NrrfXGQKvwn, ToJaRedi and 73 guests