CODEWITHSUNDEEP
vba
Emir
Emir

Reputation: 481

pasting from text document into excel comments

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?

Upvotes: 0

Views: 508

Answers (1)

Tim Williams
Tim Williams

Reputation: 166351

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

Upvotes: 1

Related Questions