Adding / Manipulating / Clearing Borders in Excel with AutoHotkey

Borders in ExcelAutoHotkey Merchandise-White Stress ballIn this tutorial I show how to add / remove / edit Borders in Excel with AutoHotkey.   Below is the code I use to apply the changes.  Be sure to review this if you’re unfamiliar with connecting to Excel via a COM object,

Function for working with Borders in Excel

;~  Note some weights and line styles are not allowed with each other
;~  Weight 1=Hairline 2=Thin 3=Med 4=Thick  ***  Line 0=None 1=Solid 2=Dash 4=DashDot 5=DashDotDot
XL_Handle(XL,1)
XL_Border(XL,RG:="a1:c1",Weight:=4,Line:=0)
XL_Border(XL,RG:="a3:c4",Weight:=3,Line:=0)
XL_Border(XL,RG:="a5:b7",Weight:=4,Line:=0)


;***********************Cell Borders (box)********************************.
XL_Border(PXL,RG="",Weight="3",Line="1"){
IfEqual,Line,0,SetEnv,Line,-4142 ; Excel constant for no border
Loop, 4{
PXL.Application.ActiveSheet.Range(RG).Borders(A_Index+6).Weight := Weight
PXL.Application.ActiveSheet.Range(RG).Borders(A_Index+6).LineStyle := Line
}}


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
}

;***borrowd & tweaked from Acc.ahk Standard Library*** by Sean  Updated by jethrow*****************
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)
}

Here is the video where I walk through using the function to play with Borders in Excel

Comments are closed.