CODEWITHSUNDEEP
vbaexcel
Charlie
Charlie

Reputation: 101

Excel VBA Insert InputBox to paste large text

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

Upvotes: 1

Views: 3080

Answers (2)

Fumu 7
Fumu 7

Reputation: 1091

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)

Upvotes: 0

jkpieterse
jkpieterse

Reputation: 2956

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

Upvotes: 2

Related Questions