grab clipboard contents in a macro (for find/replace)

[subodhjoshi]

I have to clean up snippets of text in a column. Snippets are different and I have to read contents to decide what needs to be removed.

I am trying to write a macro so that I can copy the text to be removed and run this macro ( fm a toolbar button ). It would save me lot of keystrokes/mouse clicks.

Just cannot figure out how to grab clipboard contents. Some snippet that would let me assign it to a text variable would be great. I think I can then use that text in the "What:= ____" part of replace statement.

Thanks a bunch for your help.

- Subodh

 

[subodhjoshi]

Thanks for your time again.
Unfortunately, this time, it stumbles at the FetFromClipboard stmt. I get "Object required (Error 424)". Excel help talks about confirming object qualifiers are in standard modules. Is this imilar to earlier case where I had to add MS Form 2.0 object library??

Does this run for you?

 

[Andrew Poulsom]

I missed the fact that you had a different name for your object variable, so:

Dim MyDataObj As New DataObject

should be

Dim MyObj As New DataObject

This code worked for me (if something was on the clipboard:

Sub Test1()
    Dim MyDataObj As New DataObject
    Dim MyVar As Variant
    MyDataObj.GetFromClipboard
    MyVar = MyDataObj.GetText
    MsgBox MyVar
End Sub

If you use Option Explicit at the top of your modules you will get a compile error instead of a runtime error if you use a variable that you haven't declared. Tools, Options, Editor, Require variable declaration will do it for you automatically.

 

[subodhjoshi]

THANKS A BUNCH buddy, you have saved me lots of keystrokes and eyestrain !

I was adding to complication by having another session of Excel which didnt have MS Forms object lib added !!

Thanks again.

- Subodh.