• Become a Power user Intro to AutoHotkey Intermediate AutoHotkey Intermediate Objects GUIs are Easy w/AutoHotkey Painlessly switch from V1 to V2

Setting Horizontal and Vertical Excel cell alignment with AutoHotkey

Excel cell alignment with AutoHotkey

Excel cell alignment with AutoHotkey

I love how easy it is to use AutoHotkey to set Excel formatting.   The below AutoHotkey syntax and video show how easy it is to set both vertical and horizontal alignment within Excel.  It also introduces the concepts of Excel Constants.

 
; Excel cell alignment with AutoHotkey
XL_Handle(XL,1) ;get handle to Application
;~ XL_Format_HAlign(XL,RG:="A1:C1",h:=1) ;1=Left 2=Center 3=Right
XL_Format_VAlign(XL,RG:="A1:C1") ;1=Top 2=Center 3=Distrib 4=Bottom

XL_Format_HAlign(PXL,RG="",h="1"){ ;defaults are Right bottom
IfEqual,h,1,Return,PXL.Application.ActiveSheet.Range(RG).HorizontalAlignment:=-4131 ;Left
IfEqual,h,2,Return,PXL.Application.ActiveSheet.Range(RG).HorizontalAlignment:=-4108 ;Center
IfEqual,h,3,Return,PXL.Application.ActiveSheet.Range(RG).HorizontalAlignment:=-4152 ;Right
}
; Excel cell alignment with AutoHotkey
XL_Format_VAlign(PXL,RG="",v="1"){
IfEqual,v,1,Return,PXL.Application.ActiveSheet.Range(RG).VerticalAlignment:=-4160 ;Top
IfEqual,v,2,Return,PXL.Application.ActiveSheet.Range(RG).VerticalAlignment:=-4108 ;Center
IfEqual,v,3,Return,PXL.Application.ActiveSheet.Range(RG).VerticalAlignment:=-4117 ;Distributed
IfEqual,v,4,Return,PXL.Application.ActiveSheet.Range(RG).VerticalAlignment:=-4107 ;Bottom
}

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

Video walking through Excel cell alignment with AutoHotkey

 

How to get columns from Excel and AutoHotkey | Obtain First, Last & number columns used

columns from Excel and AutoHotkey

columns from Excel and AutoHotkeyHow to get columns from Excel and AutoHotkey

Here I show you how to get columns from Excel and and AutoHotkey.  You can easily get the first column used, last column used, used range, etc.

Microsoft’s Component Object Model enables AutoHotkey to easily interact with Excel!  In the below video I used the following functions to obtain First, last and Used columns from Excel via AutoHotkey.  I also demonstrate a few additional functions which allow for easily converting letters to numbers and vice versa.  This makes it easier to program in Excel because you can reference “column M” instead of column 13.

How to get Columns from Excel and AutoHotkey

Here are the functions which make it super easy to grab / set columns in Excel

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.

Demo video to get Excel row information from Excel and AutoHotkey