Often 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********************************. XL_Find_Headers_in_Cols(PXL,Values){ 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******************* XL_Last_Col_Nmb(PXL){ 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) }