Setting Number format of Excel with AutoHotkey

Excel with AutoHotkeyAutoHotkey Merchandise-White Stress ballThis short video demonstrates the below AutoHotkey function that allows you to easily set the number format.

Video demonstrating setting Number format in Excel with AutoHotkey

Here is the function reviewed in the video and the example function calls.  Make sure you include the function of getting a pointer to the current Excel object so you can manipulate Excel with AutoHotkey.

XL_Handle(XL,1) ;1=Application 2=Workbook 3=Worksheet

;***********Examples ;#,##0   ;0,000   ;0,00.0  ;.0% ;$0 ;m/dd/yy ;m/dd ;dd/mm/yyyy
XL_Format_Number(XL,RG:="A1:B2",Format:="#,##0")
Sleep, 2000
XL_Format_Number(XL,RG:="A1:B2",Format:="0,00.00")
Sleep, 2000
XL_Format_Number(XL,RG:="A1:B2",Format:=".0%")
Sleep, 2000
XL_Format_Number(XL,RG:="A1:B2",Format:="m/dd/yy")
Sleep, 2000
XL_Format_Number(XL,RG:="A1:B2",Format:="m/dd")

;***********************Cell Number format********************************.
XL_Format_Number(PXL,RG="",format="#,##0"){
PXL.Application.ActiveSheet.Range(RG).NumberFormat := Format
}

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

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