Excel VBA - Returning Positions of all Sub-Strings within a String from the Clipboard

Question

I'm trying to find all the positions of the unique keys (followed by two Tab keystrokes) in a string in taken from clipboard, positions with which I then hope to use to insert carriage returns, and then have everything put back into the clipboard again.

First things first; getting the position part to work!

Here is a shortened example of the string:

Initial Approval in First Market or Non-Submitted Closure       090052fb842ef82f        090052fb842f3659        090052fb842ef82e

Here is the non-functional code I have put together so far from researching the problem:

Sub oldRecords()

Dim clipboard As MSForms.DataObject
Dim strContents As String

Set clipboard = New MSForms.DataObject
clipboard.GetFromClipboard
strContents = clipboard.GetText

Set objRegEx = CreateObject("vbscript.regexp")
objRegEx.Pattern = "(090052fb)[0-9A-Za-z]{8}\t\t"
objRegEx.Global = True
objRegEx.IgnoreCase = True
objRegEx.MultiLine = True

Start = 1
Do
  pos = InStr(Start, strContents, objRegEx.Execute(strContents), vbBinaryCompare)
  If pos > 0 Then
    Start = pos + Len(objRegEx.Pattern)
    WScript.Echo pos
    WScript.Echo Mid(strContents, pos, Len(objRegEx.Pattern))
  End If
Loop While pos > 0

End Sub

Right now I am getting a Run-time error '450': Wrong number of arguments or invalid property assignment, and I believe the culprit is:

objRegEx.Execute(strContents)

I'm not sure where to go from here, so any help would be fantastic! :)

Edit 1: Firstly thank you for the interest in my issue!

BrackNicku has provided a simple solution for a problem I evidently thought more complex than it needed to be! Here is the code I finally went with, adding in a few extra bits that I needed on top of the core issue:

Sub oldRecords2()
    Dim clipboard As MSForms.DataObject
    Dim strContents As String
    Dim start As Long, pos As Long

    Set clipboard = New MSForms.DataObject
    clipboard.GetFromClipboard
    strContents = clipboard.GetText
    Dim objRegEx
    Set objRegEx = CreateObject("vbscript.regexp")
    objRegEx.Pattern = "(090052fb[0-9A-Za-z]{8})\t\t"
    objRegEx.Global = True
    objRegEx.IgnoreCase = True
    objRegEx.MultiLine = True

    X1 = 10                                                                     ' Line Feed Character
    X2 = 13                                                                     ' Carriage Return Character
    X3 = "Archive Custodain Group"
    X4 = "Archive Custodain Group" & Chr(X2)
    '======================================================================================================

    strContents = Replace(strContents, Chr(X1), "")                             ' REMOVES LINE FEEDS
    strContents = Replace(strContents, X3, X4)                                  ' ADDS CR AFTER TITLE ROW

    strContents = objRegEx.Replace(strContents, "$1" & vbNewLine)
    '======================================================================================================

    clipboard.SetText strContents                                               'PUT BACK INTO CLIPBOARD
    clipboard.PutInClipboard
End Sub

Could you please add the expected result? Note that pos + Len(objRegEx.Pattern) is wrong, as you add the length of the pattern, and not the match. Probably you need to find the match itself and get its index. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jul 26, 2018 at 11:48
I got 59, 77 and 95, is that expected? Tested against strContents = "Initial Approval in First Market or Non-Submitted Closure" & vbTab & vbTab & "090052fb842ef82f" & vbTab & vbTab & "090052fb842f3659" & vbTab & vbTab & "090052fb842ef82e" & vbTab & vbTab — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jul 26, 2018 at 11:54
If I add the match output, I also get 090052fb842ef82f, 090052fb842f3659, 090052fb842ef82e. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jul 26, 2018 at 11:57

Wiktor Stribiżew · Accepted Answer · 2018-07-26 12:12:01Z

2

When you run objRegEx.Execute(strContents), it returns a match collection. Then, you are not even using the results as Len(objRegEx.Pattern) returns the length of the pattern and not the match.

It seems you just want to obtain the matches and their indices in the string. Remove all starting from Start = 1 and ending with Loop While pos > 0 and use

Dim ms As Object, m As Object
'...
objRegEx.Pattern = "(090052fb[0-9A-Za-z]{8})\t\t"
'...
Set ms = objRegEx.Execute(strContents)
For Each m In ms
    WScript.Echo m.FirstIndex
    WScript.Echo m.SubMatches(0)
Next

Tested with

strContents = "Initial Approval in First Market or Non-Submitted Closure" & vbTab & vbTab & "090052fb842ef82f" & vbTab & vbTab & "090052fb842f3659" & vbTab & vbTab & "090052fb842ef82e" & vbTab & vbTab

Result:

 59 
090052fb842ef82f
 77 
090052fb842f3659
 95 
090052fb842ef82e

Note I moved the capturing group around all but tab pattern, (090052fb[0-9A-Za-z]{8})\t\t, feel free to adjust as per your needs.

answered Jul 26, 2018 at 12:12

Wiktor Stribiżew

631k41 gold badges502 silver badges633 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wiktor Stribiżew Over a year ago

If there is a problem accessing Group 1 value, try m.SubMatches.Item(0)

BrakNicku · Accepted Answer · 2018-07-26 13:50:56Z

1

I'm trying to find all the positions of the unique keys (followed by two Tab keystrokes) in a string in taken from clipboard, positions with which I then hope to use to insert carriage returns, and then have everything put back into the clipboard again.

If you want to insert new lines before each key, then instead of locating the keys and inserting new lines, you could try RegExp.Replace

strContents = objRegEx.Replace(strContents, vbNewLine & "$1")

You have to modify the pattern to include whole key in the group:

objRegEx.Pattern = "(090052fb[0-9A-Za-z]{8})\t\t"

Result:

Initial Approval in First Market or Non-Submitted Closure
090052fb842ef82f
090052fb842f3659
090052fb842ef82e

Full code (with new line after pattern):

Sub oldRecords()
    Dim clipboard As MSForms.DataObject
    Dim strContents As String

    Set clipboard = New MSForms.DataObject
    clipboard.GetFromClipboard
    strContents = clipboard.GetText
    Dim objRegEx
    Set objRegEx = CreateObject("vbscript.regexp")
    objRegEx.Pattern = "(090052fb[0-9A-Za-z]{8})\t\t"
    objRegEx.Global = True
    objRegEx.IgnoreCase = True
    objRegEx.MultiLine = True

    strContents = objRegEx.Replace(strContents,"$1" & vbNewLine)
    'Put back to clipboard
    clipboard.SetText strContents
    clipboard.PutInClipboard
End Sub

edited Jul 26, 2018 at 13:50

answered Jul 26, 2018 at 12:22

BrakNicku

6,0313 gold badges28 silver badges40 bronze badges

3 Comments

HotSauceCoconuts Over a year ago

This sounds close to perfect, but I couldn't get it to work and I need the returns after the pattern specifically. Could you post all the code you used to make this work in an edit?

BrakNicku Over a year ago

@HotSauceCoconuts I've added the code (almost the same as yours). If you want to save the tabs - include them in brackets in pattern. If you want only CR, use vbCr instead of vbNewLine

HotSauceCoconuts Over a year ago

haha you really saved my bacon dude :D Our office will be forever indebted to you

Daniel Souza · Accepted Answer · 2018-07-26 12:22:10Z

0

Using your string example I came into this:

Sub findKeyPositions()

    Dim str As String
    Dim splitStr() As String
    Dim searchStr As String

    str = "Initial Approval in First Market or Non-Submitted Closure       090052fb842ef82f        090052fb842f3659        090052fb842ef82e        "
    splitStr() = Split(Replace(str, "090052fb", ""), "       ") 'in your example i did it with 7 spaces and not vbtab

    For i = LBound(splitStr) To UBound(splitStr)
        searchStr = Trim(splitStr(i))
        Debug.Print (InStr(1, str, searchStr, vbTextCompare))
    Next i

End Sub

answered Jul 26, 2018 at 12:22

Daniel Souza

4305 silver badges12 bronze badges

Comments

EvR · Accepted Answer · 2018-07-26 13:00:47Z

0

If I understand your post correctly you want to replace the Tabs with CR If so, then there's no need to find the positions, you could just replace.

Sub replaceTab()

Dim clipboard As MSForms.DataObject

Set clipboard = New MSForms.DataObject
clipboard.GetFromClipboard
clipboard.SetText Replace(clipboard.GetText, vbTab, vbCrLf)
clipboard.PutInClipboard

End Sub

answered Jul 26, 2018 at 13:00

EvR

3,5082 gold badges17 silver badges25 bronze badges

Collectives™ on Stack Overflow

Excel VBA - Returning Positions of all Sub-Strings within a String from the Clipboard

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related