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]
murphywsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Antagony1960Commented:
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
0
Antagony1960Commented:
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)

0
Antagony1960Commented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

murphywsAuthor Commented:
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
0
Antagony1960Commented:
Hmm... when I tested this on mine it was always the last column which was empty. So I've just taken another quick look at Outlook and realised that the user can easily change the column data by simply dragging the headers about or by right-clicking a header and customizing the view. This may not be too much of a problem if the macro is just for your own use, but if it's going to be used elsewhere it is something you need to be aware of. And even if it is only for your own use, I would still strongly recommend that you split sRows(0)--the headers row--and check it to ensure that the columns are present and in the order you expect them to be. It is very easy to accidentally drag a column to an adjacent position without realizing it.

But anyway, here's the modified snippet as you requested:
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, 5)
    For i = 0 To UBound(sRows) - 1 'Iterate the rows'
        sFields = Split(sRows(i + 1), Chr(9)) 'Split into fields'
        For j = 0 To 5 'Iterate the fields'
            sArray(i, j) = sFields(j) 'Populate the array'
        Next
    Next
    Set oData = Nothing
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
murphywsAuthor Commented:
Hi Antagony1960,
Thank you for the answer and the suggestions on how to avoid issues associated with inadvertent column customization in Outlook.
Regards,
Bill
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.