EXCEL: find string of 9 or more digits

[euras]

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 := ""

[Chunjee]

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

[mikeyww]

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.

[flyingDman]

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.

[mikeyww]

Nice! Thank you.

[hasantr]

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.
Your solutions are great.

[flyingDman]

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]

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

[AlphaBravo]

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"