• Become a Power user Intro to AutoHotkey Intermediate AutoHotkey Intermediate Objects GUIs are Easy w/AutoHotkey Painlessly switch from V1 to V2

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

;***********************Wrap text********************************.
XL_Format_Wrap(PXL,RG="",Wrap=1){ ;defaults to Wrapping

;***********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.