• Intro to AutoHotkey HotStrings with AutoHotkey Intermediate AutoHotkey GUIs are Easy with AutoHotkey Intro to DOS & AutoHotkey

Find Column Header in Excel & return location with AutoHotkey

Column Header in ExcelAutoHotkey Bottle 3Often I’ll import a file which had headers and I’ll want to determine what column specific headers are in. This AutoHotkey function shows how you can pass an Array of search terms and return the Column Header in Excel in a key-value paired object.

XL_Handle(XL,1) ; pointer to Application   XL_Handle(XL,2) Pointer to Workbook
;***********call function*******************
loc:=XL_Find_Headers_in_Cols(XL,["email","country","Age"]) ;pass search terms as an array
MsgBox % "email: "  loc["email"]   .  "`nCountry: " loc["country"]   .  "`nAge: " loc["Age"]

;********************search***Find columns based on header********************************.
Headers:={} ;need to create the object for storing Key-value pairs of search term and Location
for k, Search_Term in Values{
	Loop, % XL_Last_Col_Nmb(PXL){ ;loop over all used columns
		if (PXL.Application.ActiveSheet.cells(1,A_Index).Value=Search_Term) ;if cell in row 1, column A_index = search term
			Headers[Search_Term]:=XL_Col_To_Char(A_Index) . "1" ;set column to value in Hearders object
		}} return Headers ;return the key-value pairs Object

;******Get Handle 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
;***borrowd & tweaked from Acc.ahk Standard Library*** by Sean  Updated by jethrow*****************
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)

;***********Find last column******************* 
Return, PXL.Application.ActiveSheet.UsedRange.Columns(PXL.Application.ActiveSheet.UsedRange.Columns.Count).Column

;***********************Numeric Column to string********************************.
;~ XL_Col_To_Char(26)
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)

AutoHotkey Merchandise-White Stress ball

Here’s the video walking through the┬áColumn Header in Excel

Comments are closed.

  • Intro to AutoHotkey HotStrings with AutoHotkey Intermediate AutoHotkey GUIs are Easy with AutoHotkey Intro to DOS & AutoHotkey