Excel XLMarkSearchText

Post your working scripts, libraries and tools
aifritz
Posts: 156
Joined: 29 Jul 2018, 11:30
Location: Germany

Excel XLMarkSearchText

26 Jul 2019, 06:04

Inspired by a vba code from the ask for help forum, I translated this to ahk.
Might be useful for the one or other Excel user :)

Code: Select all

; ----------------------------------------------------------------------------------------------------------------------
; Name .........: Function XLMarkSearchText
; Category .....: Excel (Tested with Excel 2010/32Bit)
; Description ..: Marks all characters in an active Excel sheet with the given searchtext
; AHK Version ..: 1.1.30.1 x32/64 ANSI/Unicode
; Author .......: aifritz / Translated from vba to ahk by an idea from here: https://www.autohotkey.com/boards/viewtopic.php?f=76&t=66532
; Changelog ....: Jul. 26, 2019 - v0.1 - First version.
; Parameter ....: SearchText
;                 When empty in Excel an Inputbox for Searchtext is displayed
;                 When not empty all characters in all cells from are marked for the given searchtext
;
; Notes ........: - Due partial marking of numbers does not work proper, numbers are only marked when Searchtext is equal the whole number in the Excel cell
;                 - Not case sensitive
;                 - Before search begins, all colored fonts in the active sheet are set to standard = xlAutomatic
;
;
; Usage ........:
/*
Example 1: Assigned to an Hotkey with Inputbox (when parameter is empty):
F1:: ;Hotkey F1 shows Inputbox for searchtext
	XLMarkSearchText()
return

Example 2: Without Inputbox when SearchText parameter is filled:
Code...
	XLMarkSearchText("schulze") ;mark all characters in the active sheet where searchtext is found
Code...
*/

; ----------------------------------------------------------------------------------------------------------------------


XLMarkSearchText(SearchText:="")
{
	SetBatchLines, -1
	xlcolor := 0xFF ;color for marking the characters 0xFF = vbRed, 0xFF0000 = vbBlue
	try xl := ComObjActive("Excel.Application")
	catch{
		msgbox, No COM-connection to Excel possible
		return
	}
	try rngTarget := xl.ActiveSheet.UsedRange
	catch{
		ControlSend,, {esc}, ahk_class XLMAIN ;if F2 modus is active send ESC and try again
		try rngTarget := xl.ActiveSheet.UsedRange
		catch{
			msgbox, No COM-connection to Excel possible
			return
		}
	}
	If (Trim(SearchText) = ""){ ;show Inputbox for Searchtext when parameter is empty
		Inputbox, SearchText, , Find text and color the search results
		if ErrorLevel    ;if Cancel was pressed
			return
	}
	xl.Application.ScreenUpdating := False
	xlAutomatic := -4105 ;From: https://docs.microsoft.com/de-de/dotnet/api/microsoft.office.interop.excel.constants?view=excel-pia
	rngTarget.Font.ColorIndex := xlAutomatic ;reset all colored fonts

	xlPart := 2
	_ := ComObjMissing()
	if (C := rngTarget.Find(SearchText,_,_,2)){ ;find the first one
		strAddr := C.Address
		Loop {
			If isNum(C.text){ ;workaround, cause partial coloring of numbers not proper working. Coloring only when whole xl cell value is equal SearchText
     	   If (C.text = SearchText){
						C.cells.Font.Color := xlcolor
				 }
			}
			else{ ;for string coloring
				StartPos := 1
				While (InStr(C.text, SearchText,,StartPos)){
					C.Characters(InStr(C.text, SearchText,,StartPos), StrLen(SearchText)).Font.Color := xlcolor
					StartPos := InStr(C.text, SearchText,,StartPos) + StrLen(SearchText)
				}
			}
		  C := rngTarget.FindNext(C) ;find all others, if present
		} until (strAddr = C.Address) ;stop if first search result is reached again
	}
	xl.Application.ScreenUpdating := True
	xl := ""
	return
}

isNum(Str)
{
	StringReplace,Str,Str, `,, ,All  ;delete commas
	If Str is number
		return 1
	Else
		return 0
}

Edit Jul. 26, 2019: var xlColor must not be static
Ben
Posts: 13
Joined: 03 Oct 2016, 07:26

Re: Excel XLMarkSearchText

30 Jul 2019, 00:22

Thanks for sharing this unique feature. I can see a lot of situations where I can use it. It's nice that it resets the font color between runs.

I'm getting "No COM-connection to Excel possible". I closed all Excel instances in Task Manager and relaunched Excel but get the same results. I have Excel 2016 32-bit O365. I'm an Access/Excel/VBA developer and do COM automation all the time. I expected your straightforward code to work as is so I'm confused. :?

I added the Excel_Get() script to your code and now the script works fine.
https://github.com/ahkon/MS-Office-COM-Basics/blob/master/Examples/Excel/Excel_Get.ahk

Enhancement ideas:

Code: Select all

; The script can take a long time to run on a big sheet.  Show the hourglass while it's running.
xl.Application.Cursor = 2 ;  Show the xlWait cursor 
; do your thing...
xl.Application.Cursor = -4143 ;  Restore the xlDefault cursor


; The Example 1 code using the F1 key should only run when Excel is the active window.
#IfWinActive ahk_class XLMAIN
F1:: ;Hotkey F1 shows Inputbox for searchtext
	XLMarkSearchText()
return
#IfWinActive

;== OR ==
; Have this hotkey activate Excel if it does not have focus.  
SetTitleMatchMode, 2 ; 2: A window's title can contain WinTitle anywhere inside it to be a match. 
IfWinExist, Excel
{
	WinGet, winState, MinMax
	If (winState = -1)
	{
		; If the window is minimized, restore it.  
		; WinActivate is supposed to do this but doesn't always work on minimized windows prior to v1.1.20.00 March 8, 2015
		WinRestore, Excel
	}		
	WinActivate, Excel
}
It might also be nice to show a message box if no matches are found, or show a count of the number of cells affected or words highlighted, or simply a beep to let the user know it finished.

Cheers!
sleodpe
Posts: 1
Joined: 30 Jul 2019, 05:52
Contact:

Re: Excel XLMarkSearchText

30 Jul 2019, 07:38

little complicated..

Return to “Scripts and Functions”

Who is online

Users browsing this forum: Bing [Bot], CheshireCat, Jinpachi and 31 guests