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

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

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

This 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 code for Excel demonstrated in Video
Use the following for a webpage post:

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:

Creating & Connect to Excel with AutoHotkey via COM

SPSS to Excel graph in seconds– Autographs to reduce workload

SPSS to excel graph

Typically I used to use SPSS to create tables then export them and graph them in Excel (because Excel makes darn pretty graphs!)

A few years back I wrote a script that would, automatically, take SPSS tables and create the graphs for me.  This brought down my reporting time to minutes instead of hours!  Using AutoHotKey I was able to take SPSS to Excel without embedding any macros.  This is possible because AutoHotKey can connect via a COM object.

SPSS to Excel graph in seconds