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

Set Excel Font type and format with AutoHotkey

AutoHotkey Merchandise-White Stress ballAutomatically Set Excel Font type, size, emphasis with AutoHotkeySet Excel Font

Excel files are a go-to standard form many business people.  Often people will export a file from their “tool” and then spend time making it “pretty” by adjusting font size, type, cell color, formatting, etc.  When I do this, I automate the re-doing of applying formatting over and over which greatly streamlines the process and makes things more consistent.

Tutorial how to use the below AutoHotkey functions

The following AutoHotkey functions are demonstrated in the below above video.  They make it a breeze to update settings in a given Excel range.

AutoHotkey Bottle 2AutoHotkey functions to Set Excel Font type, Size and format

RControl::
Browser_Back::
;************************************************************
XL_Handle(XL,1) ;get handle to current Excel file
;~ XL_Format_Font(XL,RG:="A1:B1",Font:="Book Antiqua",Size:=20) ;Arial, Arial Narrow, Calibri,Book Antiqua
XL_Format_Format(XL,RG:="A1:B1",Bold:=0,Italic:=0,Underline:=1) ;Underline 1 thru 4

;***********************set size, type, ********************************.
XL_Format_Font(PXL,RG="",Font="Arial",Size="11"){
PXL.Application.ActiveSheet.Range(RG).Font.Name:=Font
PXL.Application.ActiveSheet.Range(RG).Font.Size:=Size
}

;***********************bold, normal, italic, Underline********************************.
XL_Format_Format(PXL,RG="",Bold=0,Italic=0,Underline=0){
PXL.Application.ActiveSheet.Range(RG).Font.Bold:= bold
PXL.Application.ActiveSheet.Range(RG).Font.Italic:=Italic
(Underline="0")?(PXL.Application.ActiveSheet.Range(RG).Font.Underline:=-4142):(PXL.Application.ActiveSheet.Range(RG).Font.Underline:=Underline+1)
}


;***********************

Excel Handles

********************************.
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)
}
RAlt::
Browser_Forward::Reload

 

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

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

Freeze panes in Excel, Toggle screen update and MRU list via AutoHotkey

Squishy Ball 2This video coverFreeze panes in Excels how to Freeze panes in Excel, Toggle screen updating (which can drastically speed-up your script) and inject a file name int the MRU (Most Recently Used) list of files.

This video walks through how to use the below AutoHotkey functions.  Be sure you check out how to get a handle to Excel which is covered in this video.

Freeze panes in Excel, Toggle screen updates & insert file into MRU

AutoHotkey Bottle 1AutoHotkey code for Excel demonstrated in Video
Use the following for a webpage post:

How to Excel row information from Excel and AutoHotkey | First row, Last row, # Used rows

Excel row informationExcel row information
How to get Excel row information from Excel and AutoHotkey

Being able to use AutoHotkey to automate Excel via COM is awesome!   In this video we walk through using these functions to obtain First row, Last row and # of used rows from Excel via AutoHotkey.

The below video demonstrates how easy it is to, programatically, obtain row information from the active worksheet.

Black Bottle 2Demo video to get Excel row information from Excel and AutoHotkey