How to do this in VBA and Excel: <<set target = Regex.execute(ActiveSheet.EntireSheet)>>

I want to use regular expressions to grab a large list of street addresses from an Excel worksheet.  

It is easy to do this when the lists are in a text file, but I don't see how to do the same with ActiveSheet.


I plan to save the sheet as a csv file then scan that, but I wonder if somebody has a better idea.

The spreadsheets are very big and performance is important, so I don't want to use iterations like For each cell in sheet.cells.


For instance, the following code scans a text file and grabs a large list.  I would like to replace lines 2 to 4 with something that gets the entire activesheet quickly.



Sub t753()
   Open "C:\Users\bob.berke\Desktop\My Master.txt" For Input As #1
    st = Input$(LOF(1), 1)
    Close #1
    
Dim regex As New regexp
Dim ans

regex.Global = True
regex.MultiLine = True
regex.IgnoreCase = True
regex.pattern = "(\t\d{3} \d{3})(.*?)(fax:)"  ' <=== the \t  means "tab", which is roughly equivalent to Excel's "beginning of cell"
Set match = regex.Execute(st)
ans = ""
For Each var In match
    ans = ans & vbCrLf & var
    
    Debug.Print var
Next
ans = Mid(ans, 3)
Dim dobj As New DataObject
dobj.SetText ans
dobj.PutInClipboard
' clipboard will have hundreds of lines that look like <<123 456   City Hall,  Cleveland, OH 44101  Phone:216-505-123 Fax:   >>
End Sub

Open in new window

LVL 5
rberkeConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Have you speed tested loading the sheet into an array and looping through that? It will be much faster than reading cell by cell.
0
rberkeConsultantAuthor Commented:
The spreadsheets contain cells that came from an internet web page

When the pattern "(\t\d{3} \d{3})(.*?)(fax:)" returns 100 matches, each of the 100 elementsmight come from multiple rows.

For instance match (5) might come from 2 rows and match(6) might come from a single row.

.
The sheet has lots of unrelated garbage between the rows that has to be ignored.

so, for Any type of iteration to work, it would first need to concatenate every cell to build a huge concatenated string.    Starting the concatenation with ary = activesheet.usedrange would certainly speed up the process, but I expect saving the spreadsheet to a .csv would be faster still.
0
rberkeConsultantAuthor Commented:
I have been looking for a solution to this kind of problem for several years and now I have it !!!  While I try to be humble, I must say that today I am most pleased with myself.

Sub Macro2()

' the following code use regular expression to parse an entire worksheet with a "Single Execution"
' it works even when the pattern spans multiple cells.
'
'
' 1) replace all tabs and linefeeds with a space and all double quotes with a tick mark. (The tick mark lets us to handle cells containing things <he said "it works">
' 2) save file as tab delimited. Excel add linefeeds after each row and double quotes around most cells.
' 3) read the resulting text file
' 4) remove the double quotes that excel added
' 5) change the Excels to be linefeeds  to be vblf & vbtab. This cause a \t pattern to be treated as "start of a cell"
'    put one extra vblf & vbtab in front of the first row.
' 6) turn the tick marks back into double quotes
' 7) put the resulting matches onto the clipboard.


   [Cells].Replace what:=chr(9), Replacement:=" ", LookAt:=xlPart
   [Cells].Replace what:=chr(34), Replacement:="`", LookAt:=xlPart
   
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs filename:="C:\aaatmp\tempfile.txt", FileFormat:= _
        xlText, CreateBackup:=False
        
        
   Open "C:\aaatmp\tempfile.txt" For Input As #1
    st = Input$(LOF(1), 1)
    st = Replace(st, chr(34), "")
    st = Replace(st, "`", chr(34))
    st = Replace(st, vbLf, vbLf & vbTab)
    st = vbLf & vbTab & st
    Close #1

Dim regex As New regexp
Dim ans

regex.Global = True
regex.MultiLine = True
regex.IgnoreCase = True
regex.pattern = "(\t\d{3} \d{3})(.*?)(fax:)"  ' <=== the \t  means "tab", which is roughly equivalent to Excel's "beginning of cell"
singeExecution: Set match = regex.Execute(st)
ans = ""
For Each var In match
    ans = ans & vbCrLf & var
    
    Debug.Print var
Next
ans = Mid(ans, 3)
Dim dobj As New DataObject
dobj.SetText ans
dobj.PutInClipboard

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rberkeConsultantAuthor Commented:
Rory's comment was valid, but I came up with a different solution that was better for my needs.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.