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

Merging, Shrinking, and Wrapping cells in Excel with AutoHotkey

Excel with AutoHotkeyAutoHotkey Bottle 3The 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

AutoHotkey Bottle 1Here 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.

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