PDA

View Full Version : [SOLVED] VBA Regex



vegard_fv
03-07-2017, 05:53 AM
Hi, I am new to Regex, and struggeling to learn. In the meantime I reach out in hope for help.

I am working with an excel-app that sends out requests for public information (FOIA) to public offices. The requests does always contain the case and documentnumber that identifies the documents at the public office. They are always in formats like this:

http://byr-journal.cloudapp.net/Images/icon-access.png (http://byr-journal.cloudapp.net/Journal/Search?department=Seksjon%20for%20budsjett%2C%20regnskap%20og%20oppf%C3%B8l ging&sectioncounter=3#)

http://byr-journal.cloudapp.net/images/data-down-arrow.png
2016/4408-95 (http://byr-journal.cloudapp.net/Journal/SearchRelated?caseYear=2016&sequenceNumber=4408) KS Regnskapsundersøkelse 2016


Dato28.02.2017SakOslo kommunes regnskap 2016FraKS


http://byr-journal.cloudapp.net/Images/icon-access.png (http://byr-journal.cloudapp.net/Journal/Search?department=Seksjon%20for%20budsjett%2C%20regnskap%20og%20oppf%C3%B8l ging&sectioncounter=3#)

http://byr-journal.cloudapp.net/images/data-down-arrow.png
2017/698-4 (http://byr-journal.cloudapp.net/Journal/SearchRelated?caseYear=2017&sequenceNumber=698) 15/2017 Spørsmål om tilknytningsavgiften for vann og avløp


Dato27.02.2017SakTilknytningsavgiftene for vann og avløpTilFinanskomiteen


http://byr-journal.cloudapp.net/Images/icon-access.png

http://byr-journal.cloudapp.net/images/data-down-arrow.png
2016/4928-3 (http://byr-journal.cloudapp.net/Journal/SearchRelated?caseYear=2016&sequenceNumber=4928) 14/2017 Verbalvedtak og takster fra bystyrets behandling av budsjett 2017


Dato27.02.2017SakOppfølging av budsjett 2017TilFinanskomiteen


http://byr-journal.cloudapp.net/Images/icon-access.png (http://byr-journal.cloudapp.net/Journal/Search?department=Seksjon%20for%20budsjett%2C%20regnskap%20og%20oppf%C3%B8l ging&sectioncounter=3#)

http://byr-journal.cloudapp.net/images/data-down-arrow.png
2016/6013-2 (http://byr-journal.cloudapp.net/Journal/SearchRelated?caseYear=2016&sequenceNumber=6013) 16/2017 Spørsmål til byrådet fra Peter Myhre(F) - Budsjettbehandling


Dato27.02.2017SakKostnader ved budsjettbehandlingenTilFinanskomiteen


http://byr-journal.cloudapp.net/Images/icon-access.png (http://byr-journal.cloudapp.net/Journal/Search?department=Seksjon%20for%20budsjett%2C%20regnskap%20og%20oppf%C3%B8l ging&sectioncounter=3#)

http://byr-journal.cloudapp.net/images/data-down-arrow.png
2017/1115-2 (http://byr-journal.cloudapp.net/Journal/SearchRelated?caseYear=2017&sequenceNumber=1115) Etterlyser urevidert regnskap - innsyn


Dato24.02.2017SakInnsyn i regnskapet - økonomisk oversikt drift, investering og balanse 2016FraKommunal rapport AS


http://byr-journal.cloudapp.net/Images/icon-no-access.png (http://byr-journal.cloudapp.net/Journal/Search?department=Seksjon%20for%20budsjett%2C%20regnskap%20og%20oppf%C3%B8l ging&sectioncounter=3#)

http://byr-journal.cloudapp.net/images/data-down-arrow.png
2016/2167-11 (http://byr-journal.cloudapp.net/Journal/SearchRelated?caseYear=2016&sequenceNumber=2167) Oversendelse av uavhengig revisors beretning om kompensasjonsoppgave for merverdiavgift for 6. termin 2016

So thats: always 4 digits identifying the year, and then always "/", followed by x number of numbers, then "-" and finally x numbers of numbers.

My excel-app extraxts each of this posts as one text-string. Could any help me write the regex - snippet I need to extract the casenumbers? The reason that I need Regex for it, is that they may appear in the middle of the text as well.

Any help much appreciated http://www.vbaexpress.com/forum/images/icons/icon7.png

Paul_Hossler
03-07-2017, 06:49 AM
Maybe something like this

It only returns the first one, but can be expanded to return all 'Finds' in an array




Option Explicit
Sub drv()
Dim s As String

s = "2016/2167-11 Oversendelse av uavhengig revisors beretning om kompensasjonsoppgave for merverdiavgift for 6. termin 2016"
MsgBox RegExpExtract(s)

s = "Oversendelse av uavhengig revisors beretning om 2016/2167123456-1122334455 kompensasjonsoppgave for merverdiavgift for 6. termin 2016"
MsgBox RegExpExtract(s)
End Sub

Function RegExpExtract(LookIn As String) As String
Dim oRegExp As Object
'Create instance of RegExp object
Set oRegExp = CreateObject("VBScript.RegExp")
With oRegExp
.Pattern = "\d{4}/\d+-\d+"
.Global = True
.IgnoreCase = False
End With

'Test to see if there are any matches
If oRegExp.test(LookIn) Then
'Run RegExp to get the matches, which are returned as a zero-based collection
RegExpExtract = oRegExp.Execute(LookIn)(0)
Else
RegExpExtract = vbNullString
End If

Set oRegExp = Nothing

End Function

vegard_fv
03-07-2017, 07:59 AM
Great, it works! Tthanks for giving me great head start. :) Super - cool!
And I'll need to work on my Regex.