Convert text from clipboard and put in cells...
 

Hi,

I receive email messages with user data which I need to paste into an
Excel worksheet. If possible I want to copy the data portion of the
email message to the clipboard and have an Excel macro convert it and
paste the different fields into a worksheet.

The text copied into clipboard have this format (fieldname | data
separator is <space:<space) :

Forename : John
Family name : Doe
Address : Sunset blvd.
City : San Fransisco

The worksheet columns are as follows:

Name | Address | City

The data from the clipboard should be processed and put into the
worksheet's current row like this:

Doe, John | Sunset blvd. | San Fransisco

Thanks a lot if someone can help us with this

regards

Geir

Convert text from clipboard and put in cells...
 

Geir,
See if this does what you need. Assumes you have copied from the email and a
Text format is available:

Private Sub CommandButton2_Click()
Dim Clip As DataObject
Dim Info As Variant

On Error GoTo Handler

Const TextFormat As Long = 1
Set Clip = New DataObject

With Clip
.GetFromClipboard
If .GetFormat(TextFormat) = True Then
Info = Split(.GetText(TextFormat), " : ")
Else
MsgBox "No text available"
Exit Sub
End If
End With

With ActiveCell
.Value = Split(Info(2), vbCr)(0) & ", " & Split(Info(1), vbCr)(0)
.Offset(0, 1).Value = Split(Info(3), vbCr)(0)
.Offset(0, 2).Value = Info(4)
End With

Exit Sub
Handler:
MsgBox "Text not in correct structu" & vbNewLine &
Clip.GetText(TextFormat)
End Sub

There is a recent thread on "microsoft.public.vb.general.discussion",
subject "Something like a Project_Activate event?" which may be useful, if
you wish the improve/extend on the above technique.

NickHK

"Geir Holmavatn" wrote in message
...
Hi,

I receive email messages with user data which I need to paste into an
Excel worksheet. If possible I want to copy the data portion of the
email message to the clipboard and have an Excel macro convert it and
paste the different fields into a worksheet.

The text copied into clipboard have this format (fieldname | data
separator is <space:<space) :

Forename : John
Family name : Doe
Address : Sunset blvd.
City : San Fransisco

The worksheet columns are as follows:

Name | Address | City

The data from the clipboard should be processed and put into the
worksheet's current row like this:

Doe, John | Sunset blvd. | San Fransisco

Thanks a lot if someone can help us with this

regards

Geir

Convert text from clipboard and put in cells...
 

On Jan 3, 8:19 am, "NickHK" wrote:
Geir,
See if this does what you need. Assumes you have copied from the email and a
Text format is available:

Private Sub CommandButton2_Click()
Dim Clip As DataObject

The DataObject is accessed by setting a reference in your VBA project
to the Microsoft Forms 2.0 object library. (Use Tools..References)

There's a useful article he

http://www.cpearson.com/excel/clipboar.htm

HTH,

Mike