Merging, Shrinking, and Wrapping cells in Excel with AutoHotkey

Excel with AutoHotkeyThe below video and AutoHotkey functions demonstrate how to connect to an Excel Object and manipulate a range of cells in Excel.  In particular I demonstrate use Excel with AutoHotkey to:

  • Wrap text (have text in cells expand so all words are visible)
  • Shrink to fit (reduce the size of the font so text fits within a given cell at it’s current size)
  • Merge cells (keep content from top-left range but merge the provided cells into one cell)

Merging, Shrinking, and Wrapping cells in Excel with AutoHotkey

Here are the functions to use Excel with AutoHotkey

 
XL_Handle(XL,1) ;get handle to Application
XL_Format_Wrap(XL,RG:="A1:B1") ;1=Wrap text, 0=no
XL_Format_Shrink_to_Fit(XL,RG:="A1",Shrink:=0) ;1=Wrap text, 0=no
XL_Merge_Cells(XL,RG:="A3:B4",Warn:=1,Merge:=1) ;set to true if you want them merged
return

;***********************Wrap text********************************.
XL_Format_Wrap(PXL,RG="",Wrap=1){ ;defaults to Wrapping
PXL.Application.ActiveSheet.Range(RG).WrapText:=Wrap
}

;***********Shrink to fit*******************
XL_Format_Shrink_to_Fit(PXL,RG="",Shrink="1"){ ;defaults to Shrink to fit
(Shrink=1)?(PXL.Application.ActiveSheet.Range(RG).WrapText:=0) ;if setting Shrink to fit need to turn-off Wrapping
PXL.Application.ActiveSheet.Range(RG).ShrinkToFit :=Shrink
}

;***********************Merge / Unm-erge cells********************************.
XL_Merge_Cells(PXL,RG,warn=0,Merge=0){ ;default is unmerge and warn off
PXL.Application.DisplayAlerts := warn ;Warn about unmerge keeping only one cell
PXL.Application.ActiveSheet.Range(RG).MergeCells:=Merge ;set merge for range
(warn=0)?(PXL.Application.DisplayAlerts:=1) ;if warnings were turned off, turn back on
}

;  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)
}

Leave a Reply

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