Convert text from clipboard and put in cells...

[Geir Holmavatn]

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

 

[NickHK]

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 structure:" & 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

 

[Mike Woodhouse]

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 here:

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

HTH,

Mike