Excel VBA Insert InputBox to paste large text

Question

I have a macro which I want to use to allow users to paste text from an Email and will automatically recognize and organize the information for filling up forms.

My problem is when it comes to make simple the "paste" process.

My idea was to insert an InputBox or a UserForm where user will be able to paste the whole Email Text. Although it didnt work out as I was expecting.

Normally when you use CTRL+V (let's say) in Range("A2"), the text will split line by line as in the email.

Is it possible to do the same but with a box prompt? Or does it only allows to insert few bits of data and only in 1 line?

My code 1)

EmailText = InputBox("Please insert Email Text Below")

    wsRep.Range("A2").Value = EmailText

'It only copies the first line

Same issue with the Prompt UserForm - NameTextBox

Could anyone please advise any other way to do it?

(I want to avoid users to have to switch between worksheets or doing anything but pasting)

Many thanks in advance.

SOLUTION:

Dim oDO As DataObject
Dim tmpArr As Variant
Dim Cell As Range
Set oDO = New DataObject
'First we get the information from the clipboard
If MsgBox("Please copy the text from the email and then press OK",      vbOKCancel) = vbOK Then
    oDO.GetFromClipboard
'Here we send the ClipBoard text to a new string which will contain all the Information (all in 1 line)
    sTxt = oDO.GetText
    wsRep.Range("A2") = sTxt 'Range is up to you

'Now we can split the email information using the "line break" and this code (found it [here][1])
    Application.Goto Reference:=wsRep.Range("A1") 'I need to move to the worksheet to run this code
'This code split each line using the criteria "break line" in rows
    For Each Cell In wsRep.Range("A2", Range("A2").End(xlDown))
    If InStr(1, Cell, Chr(10)) <> 0 Then
        tmpArr = Split(Cell, Chr(10))

        Cell.EntireRow.Copy
        Cell.Offset(1, 0).Resize(UBound(tmpArr), 1). _
            EntireRow.Insert xlShiftDown

        Cell.Resize(UBound(tmpArr) + 1, 1) = Application.Transpose(tmpArr)
    End If
    Next
    Application.CutCopyMode = False

    End If

For UserForm, did you set `Multiline` property of textbox? The default is false and you should set it to true. — Egan Wolf, Feb 15 '16 at 08:56

score 2 · Accepted Answer · answered Feb 15 '16 at 08:46

2

You could use something like this perhaps:

Sub ProcessClipboard()
'first step: Go to Tools, references and check "Microsft Forms 2.0 Object library"
    Dim oDO As DataObject
    Set oDO = New DataObject
    If MsgBox("Please copy the text from the email and then press OK", vbOKCancel) = vbOK Then
        oDO.GetFromClipboard
        MsgBox oDO.GetText
    End If
End Sub

answered Feb 15 '16 at 08:46

jkpieterse

2,727
1
9
18

I like this approach! Problem: paste the text to range A2? I tried with Range("A2").Value = oDO.GetText -- Does not make sense :D and Range("A2").PasteSpecial -- It paste it in a separate block (like an image). I am trying to figure out how to do it, any help welcome! – Charlie Feb 15 '16 at 09:58
You haven't told us what you want to do with the information which is in the clipboard. What I showed you is a way to get that information into a VBA string, so you could replace the MsgBox I gave you with sTxt = oDO.GetText and then parse the text inside sTxt in your code to do with that as needed – jkpieterse Feb 15 '16 at 10:32
I found a work-around with your suggestion :) Many thanks! – Charlie Feb 15 '16 at 12:34

score 0 · Answer 2 · answered Feb 15 '16 at 09:08

0

In an Inputbox, CR+LF(vbCrLf) separates lines. In a cell, LF(vbLf) separates lines. This difference of line separators may cause your problem.

Try following code instead of the code "My code 1)".

EmailText = InputBox("Please insert Email Text Below")

wsRep.Range("A2").Value = Replace(EmailText, vbCrLf, vbLf)

answered Feb 15 '16 at 09:08

Fumu 7

1,091
1
7
8

Sorry #Fumu_7, I didn't explain the issue adequately. – Charlie Feb 15 '16 at 12:35

Excel VBA Insert InputBox to paste large text

2 Answers2