EXCEL: find string of 9 or more digits

Get help with using AutoHotkey and its commands and hotkeys
euras
Posts: 411
Joined: 05 Nov 2015, 12:56

EXCEL: find string of 9 or more digits

30 Oct 2020, 08:21

hi, I'm trying to make that script work. Now the Find formula can give me True or False only if I add exact string, I'm looking for. But what I want to achieve, is to give me True whenever the Find formula finds a string of 9 or more digits in Excel file. It can be 123456789, or 111111111 etc. It should work somehow like AHK

Code: Select all

(\d{9,}|\d{6}-\d{3})

Code: Select all

userID := "123456789"
oWkBk := ComObjGet("C:\Users\Desktop\test.xlsx")	; get the workbook and create a handle
Loop, % oWkBk.Worksheets.Count
{
	xlDown :=	-4121	; constant to be used with the End method
	 , xlWhole :=	1	; constant to be used with the Find method
	 , col := oWkBk.Worksheets(A_Index).Range["A:CU"]
	 , sRng := oWkBk.Worksheets(A_Index).Range(col,col.End(xlDown))

	if	cell := sRng.Find[userID,,,xlWhole]	; if the pointer to a matching cell is found
		MsgBox found %A_Index%
	else
		MsgBox not found %A_Index%
}
oWkBk := ""
User avatar
Chunjee
Posts: 754
Joined: 18 Apr 2014, 19:05
GitHub: Chunjee

Re: EXCEL: find string of 9 or more digits

30 Oct 2020, 11:30

Sorry I don't see in your COM code were you run the cells though your function. Or maybe you are just trying to find the first instance of the pattern in that range.


Using https://biga-ahk.github.io/biga.ahk/#/?id=includes you can specify a regex pattern; in this case 9 digits may be ^\d{9}$. When you run the cell value against this pattern, a true or false is returned. This of course requires that every cell be run individually, like in a loop.

Code: Select all

A := new biga() ; requires https://www.npmjs.com/package/biga.ahk

string1 := "123456789"
string2 := "1234567"
string3 := "111112222"
string4 := "111112222222324123"

patternVar := "/^\d{9}$/"
msgbox, % A.includes(string1, patternVar) ; true, 9 digits
; => 1
msgbox, % A.includes(string2, patternVar) ; false, too short
; => 0
msgbox, % A.includes(string3, patternVar) ; true, 9 digits
; => 1
msgbox, % A.includes(string4, patternVar) ; false, too long
; => 0
User avatar
mikeyww
Posts: 4191
Joined: 09 Sep 2014, 18:38

Re: EXCEL: find string of 9 or more digits

30 Oct 2020, 12:40

It seems like the need is for a search function that can actually find the regex matches instead of having a script that checks every cell individually. Here's an example of an add-on.

http://www.codedawn.com/regex-find-replace-download.php

I haven't tried it. There might be a way to use or replicate it.
User avatar
flyingDman
Posts: 881
Joined: 29 Sep 2013, 19:01

Re: EXCEL: find string of 9 or more digits

30 Oct 2020, 12:59

I would not use the Excel "Find" as AHK can find more effectively. Try something like this instead:

Code: Select all

xl := ComObjActive("excel.application")     ; or open the file with COM
for c in xl.activesheet.usedrange          ; or some other range
	if c.value ~= "(\d{9,}|\d{6}-\d{3})"    
		msgbox % c.text " found in " c.address(0,0)
It finds all cells matching the pattern.
User avatar
hasantr
Posts: 749
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: EXCEL: find string of 9 or more digits

30 Oct 2020, 14:28

flyingDman wrote:
30 Oct 2020, 12:59
I would not use the Excel "Find" as AHK can find more effectively. Try something like this instead:

Code: Select all

xl := ComObjActive("excel.application")     ; or open the file with COM
for c in xl.activesheet.usedrange          ; or some other range
	if c.value ~= "(\d{9,}|\d{6}-\d{3})"    
		msgbox % c.text " found in " c.address(0,0)
It finds all cells matching the pattern.
There is a dman flying to every excel question. :D
Your solutions are great.
User avatar
flyingDman
Posts: 881
Joined: 29 Sep 2013, 19:01

Re: EXCEL: find string of 9 or more digits

30 Oct 2020, 14:57

I believe the OP wanted to search in every sheet:

Code: Select all

xl := ComObjActive("excel.application")
for sheet in XL.WorkSheets
	for c in sheet.usedrange
		if (c.value ~= "(\d{9,}|\d{6}-\d{3})")
			msgbox % c.text " found in cell " c.address(0,0) " in sheet " sheet.name
euras
Posts: 411
Joined: 05 Nov 2015, 12:56

Re: EXCEL: find string of 9 or more digits

31 Oct 2020, 15:34

great guys, that helps! thank you :) But I met another problem :)) some of the Excel files has Passwords on it. I want just skip that kind of Excel files, but I don't know how. ComObjGet automatically tries to open a file and the password prompt window appears. At that step, the script stops. I need to enter password, to make the script go further. Any ideas how to identify Excel files, which has passwords without opening them? Or at least be able to close the file if the Password popup prompts?

Code: Select all

XL := ComObjGet(A_LoopFileFullPath)
MsgBox % XL.HasPassword ; gives -1 only AFTER I enter a password
for sheet in XL.WorkSheets
	for c in sheet.usedrange
		if (c.value ~= "(\d{9,}|\d{6}-\d{3})")
			msgbox % c.text " found in cell " c.address(0,0) " in sheet " sheet.name
User avatar
AlphaBravo
Posts: 503
Joined: 29 Sep 2013, 22:59

Re: EXCEL: find string of 9 or more digits

31 Oct 2020, 18:03

euras wrote:
31 Oct 2020, 15:34
great guys, that helps! thank you :) But I met another problem :)) some of the Excel files has Passwords on it. I want just skip that kind of Excel files
use open method with password set to anything "other than the actual password" and try to open it, if it fails then skip it.

Code: Select all

_ := ComObjMissing()
xl := ComObjCreate("Excel.Application")
xl.Visible := True
Pth := "C:\Users\Desktop\test.xlsx"
try
	xl.Workbooks.Open(Pth,_,_,_,"garbage")
catch
	MsgBox % Pth " is password protected"

Return to “Ask For Help”

Who is online

Users browsing this forum: AHKStudent, Bing [Bot], c7aesa7r, Freshman, JackbobEvolved, MarcelloGammer, martin536 and 50 guests