Excel & AutoHotkey-Clear Excel content & formatting

Clear ExcelAutoHotkey Merchandise-White Stress ballIn this tutorial I demonstrate an AutoHotkey function I wrote where I clear Excel: Content, Formatting, Hyperlinks, Comments, etc.  This can be particularly helpful when you’ve got a file that either has formatting you want (or don’t want) and you want to keep the cell content intact.

Here is the Video demonstrating how to Clear Excel cells

 

The code below is what I use to Clear Excel data/formatting.  Don’t forget to include the “handle” to connect to the current Excel file.

;***********Get handle*******************
XL_Handle(XL,1) ; pointer to Application   XL_Handle(XL,2) Pointer to Workbook

;***********Call Clear function*******************
XL_Clear(XL,RG:="A1:A8",All:=0,Format:=0,Content:=0,Hyperlink:=1,Notes:=0,Outline:=0,Comments:=1) ;0 clears contents but leaves formatting 1 clears both
return

;***********Clearning various things in Excel*******************
XL_Clear(PXL,RG="",All=0,Format=0,Content=0,Hyperlink=0,Notes=0,Outline=0,Comments=0){
	; https://analysistabs.com/excel-vba/clear-cells-data-range-worksheet/  ;https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-clearcontents-method-excel
      (All=1)?(PXL.Application.ActiveSheet.Range(RG).Clear)           ;clear the range of cells including Formats
   (Format=1)?(PXL.Application.ActiveSheet.Range(RG).ClearFormats)    ;clear Formats but leave data
  (Content=1)?(PXL.Application.ActiveSheet.Range(RG).ClearContents)   ;clear Data but leave Formats
(Hyperlink=1)?(PXL.Application.ActiveSheet.Range(RG).ClearHyperlinks) ;clear Hyperlinks but leave formatting & Data
    (Notes=1)?(PXL.Application.ActiveSheet.Range(RG).ClearNotes)      ;clear Notes
  (Outline=1)?(PXL.Application.ActiveSheet.Range(RG).ClearOutline)    ;clear Outline
 (Comments=1)?(PXL.Application.ActiveSheet.Range(RG).ClearComments)   ;clear Comments
}


;***********Get Handle*******************
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)
}

Comments are closed.