Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I convert text cells to value | Excel Discussion (Misc queries) | |||
Need to convert text string to seperate cells | Excel Worksheet Functions | |||
How can I format text pased directly from clipboard to appear in correct cells | Excel Programming | |||
Convert Text String to Cells | Excel Programming |