pasting from text document into excel comments

Question

Just like you can copy an arbitrary number of lines from a text document and paste into Excel in successive rows, I want to be able to copy the lines of text and paste them into the comments of successive rows in Excel. To make it a bit easier, I paste the rows of comment text from the .txt file into a column in Excel first. This is what I'm looking at right now:

Dim myClip As New DataObject
Dim myString As String

myClip.GetFromClipboard

myString = myClip.GetText
Sheet1.Range("A1").AddComment myString

but pasting from the clipboard like this doesn't seem to have to the desired effect. Any ideas?

Tim Williams Tim Williams · Accepted Answer · 2012-07-17T16:12:38

Sub AddCommentsToSelection()

Dim myClip As New DataObject
Dim myString As String
Dim c As Range, arr, x As Integer

    myClip.GetFromClipboard
    myString = myClip.GetText
    If Len(myString) = 0 Then Exit Sub

    Set c = Selection.Cells(1)

    arr = Split(myString, vbCrLf)
    For x = LBound(arr) To UBound(arr)
        c.AddComment arr(x)
        Set c = c.Offset(1, 0)
    Next x

End Sub

pasting from text document into excel comments

1 Answers