asked on

Using VBA, how do I copy the tab delimited text contents of the clipboard as an array in Outlook 2007?

Using VBA, how do I copy the tab delimited text contents of the clipboard into an array in Outlook 2007?

The clipboard text I want to copy into the array is obtained via the following procedure:

1.      Conduct an Advanced Search in Outlook 2007
2.      Highlight all of the rows in the results window using the keyboard combination CTRL+A
3.      Copy the highlighted text to the clipboard using the keyboard combination CTRL+INSERT
4.      Use a macro to copy the tab delimited text from the clipboard into an array in Outlook 2007. The first row of text in the clipboard represents column labels and should not be saved into the array.

Sample clipboard text is shown below:

From      Subject      Received      Size      Categories      In Folder
Sender      FW: Test Message      Wed 11/5      9 KB            Inbox
Sender      RE: Test Message      Wed 11/5      9 KB            Different_Subfolder
Sender      Test Message      Wed 11/5      7 KB            Subfolder_Containing_Related_Message

For illustration purposes, I edited the display of this clipboard text by inserting [TAB] in place of the tab character.

From[TAB]Subject[TAB]Received[TAB]Size[TAB]Categories[TAB]In Folder[TAB]
Sender[TAB]FW: Test Message[TAB]Wed 11/5[TAB]9 KB[TAB][TAB]Inbox[TAB]
Sender[TAB]RE: Test Message[TAB]Wed 11/5[TAB]9 KB[TAB][TAB]Different_Subfolder[TAB]
Sender[TAB]Test Message[TAB]Wed 11/5[TAB]7 KB[TAB][TAB]Subfolder_Containing_Related_Message[TAB]

Antagony1960

This presumes you have a method for getting the clipboard text into sClipBoard:

Dim sRows, sFields, sArray() As String, i As Integer, j As Integer
sRows = Split(sClipboardText, vbCr) 'Split into rows
ReDim sArray(UBound(sRows), 5)
For i = 0 To UBound(sRows) 'Iterate the rows
sFields = Split(sRows(i), Chr(9)) 'Split into fields
For j = 0 To 5
sArray(i, j) = sFields(j) 'Populate the array
Next
Next

Antagony1960

Ok, I've had a dig around and getting the clipboard contents is fairly straightforward. You'll need to add a reference (VBA¦Tools¦References) to "Microsoft Forms 2.0 Object Library". If you can't find it, just insert and then remove a user form (VBA¦Insert¦UserForm) which should create the reference for you.

And here's the code:

Dim sClipBoard As String, oData As New DataObject
oData.GetFromClipboard
sClipBoard = oData.GetText(1)

Antagony1960

I've just noticed that you don't want the header row in the array. I've also noticed that the clipboard data contains an extra empty field on the end of each row. Also: the first split was only removing the carriage return so the first element of each row still had a line feed in it. Here's the full code again, modified to remove those unnecessary elements:

Dim sArray() As String
 
Private Sub GetClipboardArray()
Dim sRows, sFields, i As Integer, j As Integer
Dim sClipBoard As String, oData As New DataObject
    oData.GetFromClipboard
    sClipBoard = oData.GetText(1)
    sRows = Split(sClipBoard, vbCrLf) 'Split into rows
    ReDim sArray(UBound(sRows) - 1, 4)
    For i = 0 To UBound(sRows) - 1 'Iterate the rows
        sFields = Split(sRows(i + 1), Chr(9)) 'Split into fields
        For j = 0 To 4 'Iterate the fields
            sArray(i, j) = sFields(j) 'Populate the array
        Next
    Next
    Set oData = Nothing
End Sub

Open in new window

murphyws

ASKER

Thank you for this code and the description of how to create a reference to the forms library. There are five columns of data in the clipboard and the second to last onean empty column. Will you please repost your solution with the following changes so I can award the ponts?

By changing the dimensions on the array from 4 to 5 and making a similar change on the for loop I was able to copy the last column from the clipboard into the array.

Original: ReDim sArray(UBound(sRows) - 1, 4)
Revision: ReDim sArray(UBound(sRows) - 1, 5)

Original: For j = 0 To 4 'Iterate the fields
Revision: For j = 0 To 5 'Iterate the fields

ASKER CERTIFIED SOLUTION

Antagony1960

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

murphyws

ASKER

Hi Antagony1960,
Thank you for the answer and the suggestions on how to avoid issues associated with inadvertent column customization in Outlook.
Regards,
Bill