I know Excel pretty well, but am just getting to grips with VBA, and am
stuck on this problem.

I am receiving enquiries from a web form which are ending up in my email
inbox. I then copy these details (the usual stuff: name, address, telephone
etc) into cells in a spreadsheet. I've been doing this by manually copying
and pasting but I'm looking for a way to speed this up, and learn some VBA
in the process.

My thought was to copy and paste the email text/data into a text box in a
userform, then hit a button to parse the data into a spreadsheet (or perhaps
as a halfway house, just split the data up and insert it into text boxes on
the same form. I know how to update a worksheet from individual
textbox/fields).

The block of text to be parsed contains the labels like "name:" , "address:"
, "email:" etc to preface the user data, though not all of these are
compulsory, so they don't always appear. Each lump of data is separated from
the next label with 3 carriage returns.

Could someone suggest an approach to this please, or even a pointer to a web
page that might help? I've done some general searching but nothing quite
answers the question.

Thank you.

Before using the macro below, you need to build an UserForm with a TextBox
and a CommandButoon.

Suppose the TextBox is called "TextBox1".
The CommandButoon is called "CommandButton1".
Important : Set the MultiLine property of the TextBox to TRUE.

You place the following macro into CommandButton1.

'---------------------------------
Private Sub CommandButton1_Click()
Dim tmp$, tmp2 As Single, counter%
Dim tmp3%, tmp4%, tmp_string$, tmp5%
tmp = Trim(TextBox1.Text)
counter = 0
If InStr(tmp, ":") < 0 Then
tmp2 = ActiveSheet.Range("A1").CurrentRegion.Rows.Count + 1
Do
tmp3 = InStr(tmp, ":")
tmp4 = InStr(tmp3, tmp, Chr(10))
tmp5 = InStr(tmp3, tmp, Chr(13))
If tmp4 < 0 Then
If tmp5 < 0 Then
tmp_string = Mid(tmp, tmp3 + 1, _
Application.Min(tmp4, tmp5) - tmp3)
Else
tmp_string = Mid(tmp, tmp3 + 1, tmp4 - tmp3)
End If
ElseIf tmp5 < 0 Then
tmp_string = Mid(tmp, tmp3 + 1, tmp5 - tmp3)
Else
tmp_string = Right(tmp, Len(tmp) - tmp3)
End If
tmp = Right(tmp, Len(tmp) - tmp3 - Len(tmp_string))
ActiveSheet.Range("A1").Offset(tmp2, counter). _
Value = Trim(tmp_string)
counter = counter + 1
Loop While InStr(tmp, ":") < 0
End If
End Sub
'---------------------------------

To use the macro, run the userform, and paste the text into the TextBox1.
Click the CommandButton1 to fire the macro.

The strings will be broken down and placed into the spreadsheet.


Strategy used:
=============
1) From what you explained, the number of returns is not important. As there
are ":" before each piece of information anyway. So, the macro looks for the
":".
2) After locating a ":", the macro picks the string between the ":" and the
nearest return.
3) There can be two types of returns. Chr(10) and Chr(13). The macro will
take care of both.



Regards,
Edwin Tam

http://www.vonixx.com



"RunBus" wrote:

I know Excel pretty well, but am just getting to grips with VBA, and am
stuck on this problem.

I am receiving enquiries from a web form which are ending up in my email
inbox. I then copy these details (the usual stuff: name, address, telephone
etc) into cells in a spreadsheet. I've been doing this by manually copying
and pasting but I'm looking for a way to speed this up, and learn some VBA
in the process.

My thought was to copy and paste the email text/data into a text box in a
userform, then hit a button to parse the data into a spreadsheet (or perhaps
as a halfway house, just split the data up and insert it into text boxes on
the same form. I know how to update a worksheet from individual
textbox/fields).

The block of text to be parsed contains the labels like "name:" , "address:"
, "email:" etc to preface the user data, though not all of these are
compulsory, so they don't always appear. Each lump of data is separated from
the next label with 3 carriage returns.

Could someone suggest an approach to this please, or even a pointer to a web
page that might help? I've done some general searching but nothing quite
answers the question.

Thank you.

(My previous message contains an error. Fixed here.)

Before using the macro below, you need to build an UserForm with a TextBox
and a CommandButoon.

Suppose the TextBox is called "TextBox1".
The CommandButoon is called "CommandButton1".
Important : Set the MultiLine property of the TextBox to TRUE.

You place the following macro into CommandButton1.

'---------------------------------
Private Sub CommandButton1_Click()
Dim tmp$, tmp2 As Single, counter%
Dim tmp3%, tmp4%, tmp_string$, tmp5%
tmp = Trim(TextBox1.Text)
counter = 0
If InStr(tmp, ":") < 0 Then
tmp2 = ActiveSheet.Range("A1").CurrentRegion.Rows.Count
Do
tmp3 = InStr(tmp, ":")
tmp4 = InStr(tmp3, tmp, Chr(10))
tmp5 = InStr(tmp3, tmp, Chr(13))
If tmp4 < 0 Then
If tmp5 < 0 Then
tmp_string = Mid(tmp, tmp3 + 1, _
Application.Min(tmp4, tmp5) - tmp3)
Else
tmp_string = Mid(tmp, tmp3 + 1, tmp4 - tmp3)
End If
ElseIf tmp5 < 0 Then
tmp_string = Mid(tmp, tmp3 + 1, tmp5 - tmp3)
Else
tmp_string = Right(tmp, Len(tmp) - tmp3)
End If
tmp = Right(tmp, Len(tmp) - tmp3 - Len(tmp_string))
ActiveSheet.Range("A1").Offset(tmp2, counter). _
Value = Trim(tmp_string)
counter = counter + 1
Loop While InStr(tmp, ":") < 0
End If
End Sub
'---------------------------------

To use the macro, run the userform, and paste the text into the TextBox1.
Click the CommandButton1 to fire the macro.

The strings will be broken down and placed into the spreadsheet.


Strategy used:
=============
1) From what you explained, the number of returns is not important. As there
are ":" before each piece of information anyway. So, the macro looks for the
":".
2) After locating a ":", the macro picks the string between the ":" and the
nearest return.
3) There can be two types of returns. Chr(10) and Chr(13). The macro will
take care of both.



Regards,
Edwin Tam

http://www.vonixx.com



"RunBus" wrote:

I know Excel pretty well, but am just getting to grips with VBA, and am
stuck on this problem.

I am receiving enquiries from a web form which are ending up in my email
inbox. I then copy these details (the usual stuff: name, address, telephone
etc) into cells in a spreadsheet. I've been doing this by manually copying
and pasting but I'm looking for a way to speed this up, and learn some VBA
in the process.

My thought was to copy and paste the email text/data into a text box in a
userform, then hit a button to parse the data into a spreadsheet (or perhaps
as a halfway house, just split the data up and insert it into text boxes on
the same form. I know how to update a worksheet from individual
textbox/fields).

The block of text to be parsed contains the labels like "name:" , "address:"
, "email:" etc to preface the user data, though not all of these are
compulsory, so they don't always appear. Each lump of data is separated from
the next label with 3 carriage returns.

Could someone suggest an approach to this please, or even a pointer to a web
page that might help? I've done some general searching but nothing quite
answers the question.

Thank you.