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
– Egan Wolf, Commented Feb 15, 2016 at 8:56

jkpieterse · Accepted Answer · 2016-02-15 08:46:55Z

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, 2016 at 8:46

jkpieterse

3,0591 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Charlie Over a year ago

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!

jkpieterse Over a year ago

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

Charlie Over a year ago

I found a work-around with your suggestion :) Many thanks!

Fumu 7 · Accepted Answer · 2016-02-15 09:08:49Z

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, 2016 at 9:08

Fumu 7

1,0811 gold badge7 silver badges8 bronze badges

1 Comment

Charlie Over a year ago

Sorry #Fumu_7, I didn't explain the issue adequately.

Collectives™ on Stack Overflow

Excel VBA Insert InputBox to paste large text

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related