Extract string

[scottsanpedro]

Hi,
I have got email data from outlook that return info from a form entered on the web. I'm looking to extract the data and add it to specific fields on the access database. It loooks like this

Name: Mr Ian xxx

email: ixxx@tiscali.co.uk

Address1: xx, xx

Address2: xx

Town_City: xx

State_County: Oxfordshire

Postal_Code_or_Zipcode: xxxx xxx

Country: United Kingdom

Home_Telephone: 00000000

how can I get this into an array and onto the form?

Thanks in advance...Scott

 

[scottsanpedro]

I am going down the avenue of getting them to copy and paste it into a popup form and then hit a button called 'Add Details'. That will move the data from the textbox to the fields

 

[ClulessChris]

Have a look at regular expressions. Should be just the ticket.

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[HarleyQuinn]

Yep, even using a pattern as simple as " )(.*?$)" along with the second submatch would work. You could make it a lot more complex but that's probably the simplest Regex I can think of off hand that would get the job done.

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[scottsanpedro]

Thanks guys.
I found a really good piece of code that may well help other people.

Function ParseTextLinePair(ByVal strSource As String, ByVal strLabel As String)
        Dim intLocLabel As Integer
        Dim intLocCRLF As Integer
        Dim intLenLabel As Integer
        Dim strText As String = ""

        ' locate the label in the source text
        intLocLabel = InStr(strSource, strLabel)
        intLenLabel = Len(strLabel)
        If intLocLabel > 0 Then
            intLocCRLF = InStr(intLocLabel, strSource, vbCrLf)
            If intLocCRLF > 0 Then
                intLocLabel = intLocLabel + intLenLabel
                strText = Mid(strSource, _
                                intLocLabel, _
                                intLocCRLF - intLocLabel)
            Else
                intLocLabel = Mid(strSource, intLocLabel + intLenLabel)
            End If
        End If
        ParseTextLinePair = Trim(strText)
    End Function

Just search for the label and the data is extracted.
Cheers
Scott

 

[HarleyQuinn]

Here's two other versions to achieve this. The first works as yours (pass the text and the label but this version only returns the value, no colons or extra spaces) and the second will print out all the values in the immediate window (demonstrates the ease of using regular expressions, it's easy to modify how the data is output).

Solution 1:

Function ParseTextLinePair(ByVal strSource As String, ByVal strLabel As String) As String
Dim re As Object
Dim m As Variant

Set re = CreateObject("VBScript.RegExp")

With re
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "(^" & strLabel & ": )(.*?$)"
    For Each m In .Execute(strSource)
        ParseTextLinePair = m.submatches(1)
    Next m
End With

Set re = Nothing

End Function

Solution 2:

Private Sub OutPutValues(strSource as String)
Dim re As Object
Dim m As Variant

Set re = CreateObject("VBScript.RegExp")

With re
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "(^.*?: )(.*?$)"
    For Each m In .Execute(strSource)
        Debug.Print "Label = " & m.submatches(0) & ", Value = " & m.submatches(1)
    Next m
End With

Set re = Nothing

End Sub

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post