VBA Regular expression with string split

Question

Can anyone please help me out with Vba macro. I'm using the below mentioned code. The task is to read a notepad file which contains contents and extract a certain string which looks like "Z012345" and paste them in excel row wise such cell A1 will Z067859 and A2 would be Z002674 etc.,

A sample of how the contents in the notepad file looks like

Contents:

RAF0A123 Full data len= 134 

  ABATWER01 Recent change by VT0123123 on 11/12/17-11:50
                       INCLUDE(STELLER Z067859 Z002674 Z004671 Z003450 Z005433 Z023123 Z034564 Z034554 Z043212 Z010456 Z014567
                       Z027716 Z028778 Z029439 Z029876 Z035766 Z036460 Z038544 Z046456 Z047680 Z052907 Z053145 Z074674 Z094887

VBA code:

Sub Demo()
Dim myFile As String, text As String, textline As String
Dim regex As Object, str As String
Set regex = CreateObject("VBScript.RegExp")

myFile = "C:\Users\sample.txt"

Open myFile For Input As #1

With regex
  .Pattern = "Z0[0-9]+"
  .Global = Trueq
End With

Set matches = regex.Execute(Input)

For Each Match In matches
Range("A1:A4000").Value = Match.Value
Next Match

  Do Until EOF(1)
  Line Input #1, textline
  text = text & textline
  Loop

  Close #1

End Sub

Expected output:

Excel output column A should contain the below:

Could anyone help me out to write a macro to perform the task?

`Trueq`? Please fix the typos and explain what the issue is with your code. — Wiktor Stribiżew, Aug 24 '18 at 10:33
Every value (except the first one - STELLER) will match your regex. Do you want to paste them sequentially in column A? Or do you want to sort the list first and then paste? What exactly is the issue you are trying to fix here — Pankaj Jaju, Aug 24 '18 at 10:41
Hi Pankaj, I want to paste the values in column A sequentially. I don't want to sort it. I want something like, Z067859 Z002674 Z004671 Z003450 Z005433 Z023123 Z034564 Z034554 Z043212 Z010456 "Z014567 " Z027716 Z028778 Z029439 Z029876 Z035766 Z036460 Z038544 Z046456 Z047680 Z052907 Z053145 Z074674 Z094887 — Patrick, Aug 24 '18 at 11:08
Some basic troubleshooting. If you step through your code, you will see that your `regex.execute` statement is done against an empty string. Since you chose to NOT require variable declaration (see `tools/options/editor`), you have effectively set `Regex.global = FALSE` (`Trueq` evaluates to `False`). Put `Option Explicit` at the start of your macro; set a break point; and go through step-by-step examining the variable contents at each step. — Ron Rosenfeld, Aug 24 '18 at 11:09

Hambone · Answer 1 · 2018-08-24T11:55:04.520

I actually think your code is 85% there. I see a couple of things wrong.

1) You need to read the file before you try to output to Excel. In your code it seems you read the file after any activity in Excel

2) You are putting the same value in every single cell from A1 to A1000, overwriting them each time. I believe you want to loop down and put each value in a cell.

3) You're passing a variable that doesn't even exist to your regex

A couple of changes, and this might do it:

Sub Demo()
Dim myFile As String, text As String, textline As String
Dim regex As Object, str As String
Set regex = CreateObject("VBScript.RegExp")

myFile = "C:\Users\sample.txt"

Open myFile For Input As #1
  Do Until EOF(1)
  Line Input #1, textline
  text = text & textline
  Loop
Close #1

With regex
  .Pattern = "Z0[0-9]+"
  .Global = True
End With

Set matches = regex.Execute(text)

Dim row As Long
row = 1

For Each Match In matches
  Cells(row, 1).Value2 = Match
  row = row + 1
Next Match


End Sub

`Dim row As Long` if you handle row counts, because Excel has more rows than `Integer` can handle. It is recommended [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) as there is no benefit in using `Integer` at all in VBA. — Pᴇʜ, Aug 24 '18 at 11:44
You are absolutely right -- I should know better. Old habits. — Hambone, Aug 24 '18 at 11:55

Pankaj Jaju · Answer 2 · 2018-08-24T11:50:17.693

Rather than reading one line at a time, I would rather read the entire file into a string and then find the string and paste it. Sample code

Dim myFile As String, regex As Object, str As String, ctr As Long

myFile = "C:\Users\sample.txt"
With CreateObject("Scripting.FileSystemObject")
    str = .OpenTextFile(myFile, 1).ReadAll
End With

Set regex = CreateObject("VBScript.RegExp")
With regex
  .Pattern = "Z0[0-9]+"
  .Global = True
End With
Set matches = regex.Execute(str)

ctr = 1
For Each Match In matches
  Sheet1.Range("A" & ctr).Value2 = Match
  ctr = ctr + 1
Next Match

score 0 · Answer 3 · answered Aug 25 '18 at 04:44

Please try the below and let me know it meets your requirement

Sub Demo()

Dim myFile As String, text As String, textline As String
Dim str As String
Dim LineArray() As String
Dim DataArray() As String
Dim TempArray() As String
Dim rw As Long, col As Long
Dim FileContent As String

Set regex = CreateObject("vbscript.regexp")

Dim allMatches As Object
Delimiter = " "

myFile = "Path\sample.txt"

    With regex
    .Pattern = "Z0[0-9]+"
    .Global = True
    End With

Open myFile For Input As #1

  Do Until EOF(1)
  Line Input #1, textline
  text = text & textline
  Loop

LineArray() = Split(text, vbCrLf)
i = 1
For x = LBound(LineArray) To UBound(LineArray)
    If Len(Trim(LineArray(x))) <> 0 Then
        TempArray = Split(LineArray(x), Delimiter)

        col = UBound(TempArray)
        ReDim Preserve DataArray(col, rw)
        For y = LBound(TempArray) To UBound(TempArray)
          Set allMatches = regex.Execute(TempArray(y))
          Range("A" & i).Value = allMatches.Item(0)
          i = i + 1
        Next y
    End If

      rw = rw + 1

  Next x

  Close #1

End Sub

Thanks

VBA Regular expression with string split

3 Answers3