How to paste then nth item on the Office Clipboard?

[foverman]

Hi -

After a fair amount of research on various methods of accessing the clipboard, I'm drawing a blank on this.

Say that I've copied several things to the Office Clipboard and now I want VBA to paste the first five items into a worksheet -- how do I do that?

I've run across other situations before where this would be useful. Here are the current particulars. I'm looking up employee data and copying these five items to the clipboard:

10123412
Overman, Frank (MyCompany - US/MyCity)
frank.overman@mycompany.com
Manager, Some (10123406)
foverman

In Excel I'd like VBA to paste these five values into columns. The row and column references I can handle. I just need to know how to access these specific items on the clipboard.


Various methods that use Clipboard.GetDataObject() seem only to deal with the most recent item copied.

I ran across a reference to ClipEx (Karl E. Peterson's Classic VB Code: ClipEx) but don't find enough information on it. Don't know if it will do what I want or not.

Help, please?

 

[mikerickson]

Perhaps something like this.
ClipItems is a zero based array of the substrings of a linefeed delimited string in the clipboard.

Sub test()
    Dim clipData As New DataObject
    Dim clipString As String, clipItems As Variant
    
    #If Mac Then
        clipData.SetText MacScript("the clipboard")
    #Else
        clipData.GetFromClipboard
    #End If
    
    clipString = clipData.GetText
    clipString = Replace(clipString, vbLf, vbCr)
    clipItems = Split(clipString, vbCr)
    
    With Range("A1").Resize(1, UBound(clipItems) + 1)
        .Value = clipItems
    End With
End Sub

 

[foverman]

Hi Mike,

Thanks for the reply.

A workaround like this is one approach. I'm not sure it really buys me much tho. When I collect the five pieces of data, I'd have to paste them someplace as a linefeed delimited list, then copy the list for the macro to use.


Any other ideas? Anyone?

 

[mikerickson]

I'm not sure why you are using the Clipboard. Excel has Public (persistent) arguments, cells, Names and DocumentProperties to stash strings until needed, without using the Clipboard.

My concern would be:
1) if the delimited string is created, processed and used with a single invocation of VB by the user, VB arguments would be the way to to (IMO)

2) if the string is created by several actions by user, (e.g. select number, press button, select name, press another button,...) storing in the Clipboard means that the user can't use the clipboard for their normal purpose until they have finished pressing theses buttons all in the proper order.

I'd avoid putting things in the clipboard.

 

[foverman]

In my research on this, I've seen similar statements about avoiding the clipboard.

I'd avoid putting things in the clipboard...the user can't use the clipboard for their normal purpose until they have finished...

In this case, I'm the user. The clipboard doesn't need to be used for anything else while this data is being collected. Expanding on this statement from my original post

I'm looking up employee data and copying these five items to the clipboard:

I'm looking up employee data via web browser, finding the information on two different web pages, one of which does not have a URL that would lend it self to a web query.

I look up the user by surname and on this page:

• https://mycompany-net.net/gms/db/ep.browse

pick out the right listing and copy the data

• 10123412
• Overman, Frank (MyCompany - US/MyCity)
• foverman

then I click a link which takes me to the page:

• https://mycompany-net.net/gms/db/im.lookup?entry=10123412

and copy the data

• Manager, Some (10123406)
• frank.overman@mycompany.com

Once everything is collected, I return to Excel and start pasting.

I hope what I've described is clear.
Do you see a way to get the data without using the clipboard?

Since I have to go to the URL https://mycompany-net.net/gms/db/ep.browse and search manually, I don't see any way to use a query.

 

[mikerickson]

This will let you decide whether to store the delimited string or each of the sub-strings.

Sub test()
    Dim clipData As New DataObject
    Dim clipString As String, clipItems As Variant
    
    Rem fill dataobject with clipboard
    #If Mac Then
        clipData.SetText MacScript("the clipboard")
    #Else
        clipData.GetFromClipboard
    #End If

    Rem extract contents from dataobject
    clipString = clipData.GetText
    
    Rem process delimiters
    #If Mac Then
        clipString = Replace(clipString, vbCr, vbLf)
        clipItems = Split(clipString, vbCr)
    #Else
        clipString = Replace(clipString, vbCr, vbLf)
        clipItems = Split(clipString, vbLf)
    #End If
    
    Rem store delimited string
    Range("A1").Value = clipString
    
    Rem store sub-strings
    With Range("A2").Resize(1, UBound(clipItems) + 1)
        .Value = clipItems
    End With
End Sub

 

[mikerickson]

I just had another thought. If you want to access the fourth item in the delimited list, in the posted code, you can reference the array element clipItems(3) without writing to the sheet.

 

[foverman]

Hi Mike,

Thanks for your replies.

Still not seeing a way to get clipString to contain anything other than the last item copied.