This post continues to build on using AutoHotkey via COM to manipulate Excel. This time I demonstrate how easy it is to use an AutoHotkey function to insert columns, delete columns and set column widths. All of which are utilizing a simple function after connecting to Excel via AutoHotkey and COM.
Here’s the video demonstrating how to manipulate columns in Excel via AutoHotkey.
Here is the code where I demonstrate in the above video how to manipulate columns in Excel via AutoHotkey. Make sure you connect to the Excel object before calling the functions!
#SingleInstance,Force RAlt::Reload RControl:: XL_Handle(XL,1) ;get handle to Application XL_Col_Width_Set(XL,RG:="A:B=-1|D:F=-1|H=15|K=3") ;-1 is auto XL_Col_Insert(XL,RG:="B:C",WD:=8) ;insert column(s) and set width XL_Col_Delete(XL,RG:="A|D:F|Z|BB:BC") ;Delete columns return ;***********column width******************* XL_Col_Width_Set(PXL,RG=""){ for k, v in StrSplit(rg,"|") ;Iterate over array (StrSplit(v,"=").2="-1")?(PXL.Application.ActiveSheet.Columns(StrSplit(v,"=").1).AutoFit):(PXL.Application.ActiveSheet.Columns(StrSplit(v,"=").1).ColumnWidth:=StrSplit(v,"=").2) } ;***********************Column Insert********************************. XL_Col_Insert(PXL,RG="",WD:="5"){ ;Default width is 5 PXL.Application.ActiveSheet.Columns(RG).Insert(-4161).Select PXL.Application.ActiveSheet.Columns(RG).ColumnWidth:=WD } ;***********************Column Delete********************************. XL_Col_Delete(PXL,RG=""){ for j,k in StrSplit(rg,"|") (instr(k,":")=1)?list.=k ",":(list.=k ":" k ",") ;need to make for two if only 1 col PXL.Application.ActiveSheet.Range(SubStr(list,1,(StrLen(list)-1))).Delete ;use list but remove final comma } ; 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) }