Using The ClipBoard In A Macro By Threading

How do I access the Clipboard in a Macro? Just using GetData and SetDataObject does not work. This article will show you how.


You would think that the Clipboard object would work the same in a Macro as it does in an application. That would not be correct. If you simply try to use the clipboard in a Macro, your code will fail due to the way that macros work at the behest of the IDE. The error you will get is the old "object not set to an instance of an object" message. Therefore, to make a long story short, you need to create a separate thread to access the Clipboard.


First, make sure that the following Imports are present in the Macro Module that you will use for your sample macro to access the Clipboard.


Imports System.Threading


Imports System.Windows.Forms


Next, place the following module level variable in the module.


Private clipString As String = String.Empty


Next, create a method in the Module to retrieve the contents of the Clipboard as follows:


Public Sub GetClipboard()


Dim cbThread As Threading.Thread = _


New Threading.Thread(AddressOf GetClipboardText)


With cbThread


.ApartmentState = ApartmentState.STA


.IsBackground = True


.Start()


' Wait for clipboard action


.Join()


End With


cbThread = Nothing


' at this point, the contents of the Clipboard will be in the


' module level variable clipString


End Sub


Finally, create a method that will place text on the Clipboard as shown below:


Public Sub SetClipboard(ByVal text As String)


' put the results back on the clipboard


Dim ClipBoardThread As Threading.Thread = _


New Threading.Thread(AddressOf PutTextOnClipboard)


With ClipBoardThread


.ApartmentState = ApartmentState.STA


.IsBackground = True


.Start()


' Wait for clipboard action


.Join()


End With


ClipBoardThread = Nothing


End Sub


The following methods are called by the two threads created in the code shown above.


Sub GetClipboardText()


clipString = _


Clipboard.GetDataObject() _


.GetData(System.Windows.Forms.DataFormats.StringFormat)


End Sub


Sub PutTextOnClipboard()


Clipboard.SetDataObject(clipString, True)


End Sub