Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

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]
0
murphyws
Asked:
murphyws
  • 4
  • 2
1 Solution
 
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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
 
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now