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

#SingleInstance,Force
Browser_Forward::Reload
Browser_Back::

XL_Handle(XL,1) ;get handle to Application
;***********freeze panes in Excel*******************
XL_Freeze(XL,Row:="") ;turns off freeze panes
XL_Freeze(XL,Row:="1",Col:="B") ;freeze first row and first column
XL_Freeze(XL,Row:="2",Col:="b") ;just freeze  columns at b
XL_Freeze(XL,Row:="1",Col:="b") ;just feeze rows at 1

;***********Togle Screen update*******************
XL_Toggle_Screen_Update(XL)
loop, 10{
XL.Application.ActiveSheet.Range("A" A_Index).Interior.ColorIndex :=a_index
sleep, 50
}
XL_Toggle_Screen_Update(XL)
;***********MRU - Most recently Used files*******************
XL_MRU(FileName:="B:\Diet.xlsx")
return
;***********************Freeze Panes in Excel********************************.
XL_Freeze(PXL,Row="",Col="A"){
PXL.Application.ActiveWindow.FreezePanes := False ;unfreeze in case already frozen
IfEqual,row,,return ;if no row value passed row;  turn off freeze panes

PXL.Application.ActiveSheet.Range(Col . Row+1).Select ;Helps it work more intuitivly so 1 includes 1 not start at zero
PXL.Application.ActiveWindow.FreezePanes := True
}
;***********************Screen update toggle********************************.
XL_Toggle_Screen_Update(PXL){
   PXL.Application.ScreenUpdating := ! PXL.Application.ScreenUpdating
}
;***********************MRU*********************************.
XL_MRU(FileName=""){
XL_Handle(XL,1)
XL.RecentFiles.Add(FileName) ;adds file to recently accessed file list
For file in ComObj("Excel.Application").RecentFiles
(A_Index<>1)?mruList.Insert(file.name): ;if A_Index doesn't =1, insert filename
mruList.Insert(RegExReplace(Filename,"^[A-Z]:")) ;adds to MRU list
}
;  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)
}

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.