[RESOLVED] Copy column of items in clipboard to an array

[fitties]

I would like to create an array of strings that is created from the items in the clipboard.

Example text:

Item Name ABC
Item Name 123
Item Name QWE
Item Name 456

Say the above cells data I've copied into the clipboard. I'd like to have a loop look at each item in my clipboard and assign it to an array, say myStrArray.

Basically i'll have:

Item Name ABC = myStrArray0
Item Name 123 = myStrArray1
Item Name QWE = myStrArray2
Item Name 456 = myStrArray3

Eventually I will be using this in a bigger script but I just cant find/figure out how to do this. Could anyone help me? Your help is greatly appreciated.
I have searched around the net for this and can't find it. Is this possible?

[westconn1]

use the split function
try

mystrarray = split(mystr, vbnewline)

where the clipboard data is inputted into mystr

you don't say how you are getting the data from the clipboard

[fitties]

I was going to use the following code:

Set MyData = New DataObject
MyData.GetFromClipboard
myStr = MyData.GetText(1)

Do you think this will work? I will either be copying from notepad where all my data will be in a row by row entry into the cell, or from excel and in column format. Thanks for your help! =)

[westconn1]

i believe it should work, but you will need to test
data copied from excel should be a string separated by tabs for column and newline for row

[fitties]

testing right meow! Will let you know the results. Thank you!

[fitties]

That seems to be working great! Let me know if i could improve the code i have so far:

Public Sub ec_es_validation()
    Dim MyData As DataObject
    Dim myStr As String
    Dim myStrArray As Variant
    Dim i As Long
    
    Set MyData = New DataObject
    MyData.GetFromClipboard
    myStr = MyData.GetText(1)
    On Error GoTo Error_Handling
    
    myStrArray = Split(myStr, vbNewLine)
    
    For i = LBound(myStrArray) To UBound(myStrArray)
        Debug.Print myStrArray(i)
        'do more within For loop

    Next i

ExitHere:
    Exit Sub

Error_Handling:
    MsgBox "The clipboard is empty!", vbCritical
    Resume ExitHere

End Sub

Eventually i want to be able to search a worksheet by the myStrArray(i), if found, get the column its in paste that into a new spreadsheet.sheet1 "A - myStrArray(i)" / if not found it pastes a row on a new spreadsheet.sheet2 in the next unused row. id like the script to be smart enough to know "if spreadsheet is open, paste row on correct sheet, if not open, open excel instance and then paste."

[fitties]

I'll post further questions on my script in another post when needed. Thanks for your help westconn1