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

Leave a Reply

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