VBA - Regex - String in a Word
VBA - Regex - String in a Word
I'm having this type of json response:
"success":true,"data":["guid":10101,"name":"name1","ispool":true,"dateadded":"2018-09-12T10:22:44","status":5,"lastactivity":"2018-09-13T03:15:06","templatechannels":["guid":10102,"name":"name2","iscampaign":false,,"ispool":true,"dateadded":"2018-09-12T10:22:44","status":5,"lastactivity":"2018-09-13T03:15:06","templatechannels",........]]
I want to get all guid from this response...
It may have more than 100 records of guid. I want to have all of them.
A quick google of "VBA JSON" found this library which looks pretty easy to use.
– Shawn
Sep 15 '18 at 6:29
Yeah but this is a string not json in actual.
– Softhmak.
Sep 15 '18 at 6:39
That's what JSON usually is, yes.
– Shawn
Sep 15 '18 at 6:46
2 Answers
2
This is regex based reading your string from a cell. If there can also be guids which you want then change the pattern passed to guids?":(d+[^,]).
guids
guids?":(d+[^,])
Option Explicit
Public Sub test()
Dim s As String, i As Long, arr()
s = [A1]
arr = GetMatches(s, "guid"":(d+[^,])")
For i = LBound(arr) To UBound(arr)
Debug.Print arr(i)
Next
End Sub
Public Function GetMatches(ByVal inputString As String, ByVal sPattern As String) As Variant
Dim matches As Object, iMatch As Object, s As String, arrMatches(), i As Long
With CreateObject("vbscript.regexp")
.Global = True
.MultiLine = True
.IgnoreCase = True
.pattern = sPattern
If .test(inputString) Then
Set matches = .Execute(inputString)
ReDim arrMatches(0 To matches.Count - 1)
For Each iMatch In matches
arrMatches(i) = iMatch.submatches.item(0)
i = i + 1
Next iMatch
Else
ReDim arrMatches(0)
arrMatches(0) = vbNullString
End If
End With
GetMatches = arrMatches
End Function
Regex:
Try it here.
/
guid":(d+[^,])
/
gm
guid": matches the characters guid": literally (case sensitive)
guid":
guid":
1st Capturing Group (d+[^,])
(d+[^,])
d+ matches a digit (equal to [0-9])+ Quantifier — Matches between one and unlimited times, as many times as possible, giving back as needed (greedy)
d+
digit (equal to [0-9])
+
Match a single character not present in the list below [^,], matches the character , literally (case sensitive)
[^,]
,
I extract the first group submatch.
Thanks a lot, this is what I need. I really appreciate your efforts. Thanks man.
– Softhmak.
Sep 15 '18 at 16:10
how to iterate now ? like I declare string as Dim ptrn As String... then in the next line am doing this for ptrn = "" + allThings(k) + """:(d+[^,])""".... It is giving me errors now ?
– Softhmak.
Sep 15 '18 at 18:46
and than arr = GetMatches(rsp, ptrn) will be used than for each iteration.
– Softhmak.
Sep 15 '18 at 18:48
I'm sorry I don't understand. The regex will examine the string and write all the matches to the immediate window Ctrl + G. You replace [A1] with the source of the string.
– QHarr
Sep 15 '18 at 18:48
For k = 1 To 40 If allThings(k) <> "" Then Dim rsp As String, ptrn As String, i As Long, arr() rsp = hReq.ResponseText ptrn = "" + allThings(k) + """:(d+[^,])""" arr = GetMatches(rsp, "guid"":(d+[^,])") For i = LBound(arr) To UBound(arr) MsgBox arr(i) Next End If Next
– Softhmak.
Sep 15 '18 at 18:50
if you have that string, say, in excel cell A1 you could use this:
Dim arr As Variant
Dim iArr As Long
arr = Split(Range("A1").Value, "guid")
If UBound(arr, 1) > 0 Then
For iArr = 1 To UBound(arr, 1)
MsgBox Mid(arr(iArr), 3, InStr(arr(iArr), ",") - 3)
Next
End If
edit after OP's comment
to face the "guids" vs "guid" occurrence you could first change all "guids" to "guid" with Replace() function
arr = Split(Replace(Range("A1").Value, "guids", "guid"), "guid")
Nice work around but what if I have some other columns with same name but just one character more like "guids" ? it will read 'guids' too with 'guid'... what to do in that case?
– Softhmak.
Sep 15 '18 at 6:48
well that would be another question while this one could be considered solved...
– DisplayName
Sep 15 '18 at 6:51
gimme some hint please..... am new to VBA though..
– Softhmak.
Sep 15 '18 at 6:53
I’ll think it over. While you really could post a new question stating ALL your needs
– DisplayName
Sep 15 '18 at 7:19
As long as “guids” vs “guid” is the additional issue, you could first change all “guids” to “guid” with Replace() function and then go on with this solution
– DisplayName
Sep 15 '18 at 7:41
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy
You need a JSON parser, not regular expressions.
– Shawn
Sep 15 '18 at 6:24