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

 

[Andrew Poulsom]

Why do you need a macro?

Select the text you want to replace and press Ctrl+Insert to put it on the ClipBoard. Choose Edit Replace and while in the What box press Shift+Insert. Then continue as normal.

 

[subodhjoshi]

Yes, thats what I am doing right now. I have to do this several times ( at least 25 times )per column, about 30 columns to go per file.

If I have a macro, I can copy text to be removed to cliboard, invoke macro fm a toolbar button - 2keys+1 click against 6keys+1 click.

- Subodh

 

[Andrew Poulsom]

See:

http://www.cpearson.com/excel/clipboar.htm

 

[subodhjoshi]

got debugger error for statement;
Dim MyDataObj As New DataObject

Are 'objects' allowed only in forms??

 

[subodhjoshi]

MyObj.GetFromClipboard - from the link that you gave earlier - is where I am stuck.

How can I confirm that GetFromClipboard is working all right from me?
- Subodh

 

[Andrew Poulsom]

Quote from link I gave you:

"Because these procedures use the DataObject variable type, you must have a reference set in your VBA project to the Microsoft Forms 2.0 object library."

 

[subodhjoshi]

did that. Still the same error.

Sure appreciate your patience.

 

[subodhjoshi]

Can you spot the error in the code below? WHen I run it, I get "runtime error #91" for teh MyObj.GetFromClipboard statement.
------------------------------------

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 10/22/2002 by sjoshi
'

Dim MyObj As Object


Dim MyVar As Variant

MyObj.GetFromClipboard

MyVar = MyObj.Text


Cells.Replace What:=MyVar, Replacement:="DDD", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False
End Sub

 

[Andrew Poulsom]

Yes.

Dim MyObj As Object

should be

Dim MyDataObj As New DataObject