Obtain First, Last and # Used columns from Excel via AutoHotkey and COM

columns from Excel via AutoHotkey

Microsoft’s Component Object Model enables AutoHotkey to easily interact with Excel!  In the below video I used the following functions to obtain First, last and Used columns from Excel via AutoHotkey.  I also demonstrate a few additional functions which allow for easily converting letters to numbers and vice versa.  This makes it easier to program in Excel because you can reference “column M” instead of column 13.

Here are the functions which make it super easy to grab / set columns in Excel

XL_Handle(XL,1) ;get handle to Excel Application

MsgBox % "First Col number: " XL_First_Col_Nmb(XL)
MsgBox % "First Col Alpha: "  XL_First_Col_Alpha(XL)
MsgBox % "Last Col number: " XL_Last_Col_Nmb(XL)
MsgBox % "Last Col Alpha: "  XL_Last_Col_Alpha(XL)
MsgBox % "Used  Col Number: " XL_Used_Cols_Nmb(XL)

MsgBox % XL_Col_To_Char(13)
MsgBox % XL_String_To_Number("aa")

;Columns from Excel via AutoHotkey
;***********************First Column********************************.
XL_First_Col_Nmb(PXL){
   Return, PXL.Application.ActiveSheet.UsedRange.Columns(1).Column
}

;***********************First Column Alpha**********************************.
XL_First_Col_Alpha(PXL){
FirstCol:=PXL.Application.ActiveSheet.UsedRange.Columns(1).Column
IfLessOrEqual,LastCol,26, Return, (Chr(64+FirstCol))
	Else IfGreater,LastCol,26, return, Chr((FirstCol-1)/26+64) . Chr(mod((FirstCol- 1),26)+65)
}

;***********************Used Columns********************************.
XL_Used_Cols_Nmb(PXL){
Return, PXL.Application.ActiveSheet.UsedRange.Columns.Count
}

;***********************Last Column********************************.
XL_Last_Col_Nmb(PXL){
Return, PXL.Application.ActiveSheet.UsedRange.Columns(PXL.Application.ActiveSheet.UsedRange.Columns.Count).Column
}

;***********************Last Column Alpha**  Needs Workbook********************************.
XL_Last_Col_Alpha(PXL){
LastCol:=XL_Last_Col_Nmb(PXL)
IfLessOrEqual,LastCol,26, Return, (Chr(64+LastCol))
	Else IfGreater,LastCol,26, return, Chr((LastCol-1)/26+64) . Chr(mod((LastCol- 1),26)+65)
}


;***********************Numeric Column to string********************************.
XL_Col_To_Char(index){ ;Converting Columns to Numeric for Excel
	IfLessOrEqual,index,26, Return, (Chr(64+index))
	Else IfGreater,index,26, return, Chr((index-1)/26+64) . Chr(mod((index - 1),26)+65)
}

;***********************alpha to Number********************************.
XL_String_To_Number(Column){
    StringUpper, Column, Column
    Index := 0
    Loop, Parse, Column  ;loop for each character
    {ascii := asc(A_LoopField)
    	if (ascii >= 65 && ascii <= 90)
           index := index * 26 + ascii - 65 + 1    ;Base = 26 (26 letters)
        else { return
        } }
        return, index
}

;  Function for connecting to Excel
XL_Handle(ByRef PXL,Sel){
ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN ;identify the hwnd for Excel
IfEqual,Sel,1, Return, PXL:= ObjectFromWindow(hwnd,-16).application ;Handle to Excel Application
IfEqual,Sel,2, Return, PXL:= ObjectFromWindow(hwnd,-16).parent ;Handlle to active Workbook
IfEqual,Sel,3, Return, PXL:= ObjectFromWindow(hwnd,-16).activesheet ;Handle to Active Worksheet
}
;***********adapted from ACC.ahk*******************
ObjectFromWindow(hWnd, idObject = -4){
(if Not h)?h:=DllCall("LoadLibrary","Str","oleacc","Ptr")
 If DllCall("oleacc\AccessibleObjectFromWindow","Ptr",hWnd,"UInt",idObject&=0xFFFFFFFF,"Ptr",-VarSetCapacity(IID,16)+NumPut(idObject==0xFFFFFFF0?0x46000000000000C0:0x719B3800AA000C81,NumPut(idObject==0xFFFFFFF0?0x0000000000020400:0x11CF3C3D618736E0,IID,"Int64"),"Int64"), "Ptr*", pacc)=0
 Return ComObjEnwrap(9,pacc,1)
}

Using AutoHotkey to get Columns from Excel via AutoHotkey

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.