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

Create / Connect to Excel with AutoHotkey via COM

excel-with-autohotkey

AutoHotkey Merchandise-White Stress ballAutomating Excel with AutoHotkey is pretty easy however first connecting with the Excel object can be a bit confusing.

This video walks through both connecting with a running Excel application and creating a new one.  It also walks you through how to set the object visible (and use the Task Manager to see if it exists)

Here is the code reviewed in the video:

#SingleInstance,, Force
Browser_Forward::Reload
Browser_Back::

try XL := ComObjActive("Excel.Application") ;handle to running application
Catch {
    MsgBox % "no existing Excl ojbect:  Need to create one"
XL := ComObjCreate("Excel.Application")
XL.Visible := 1 ;1=Visible/Default 0=hidden
}
XL.Visible := 1 ;1=Visible/Default 0=hidden
MsgBox % "is an object? " IsObject(XL)

Creating & Connecting to Excel with AutoHotkey via COM