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

subodhjoshi

Board Regular
Joined
Oct 22, 2002
Messages
51
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Oct 22, 2002
Messages
51
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
 

subodhjoshi

Board Regular
Joined
Oct 22, 2002
Messages
51

ADVERTISEMENT

got debugger error for statement;
Dim MyDataObj As New DataObject

Are 'objects' allowed only in forms??
 

subodhjoshi

Board Regular
Joined
Oct 22, 2002
Messages
51
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

Board Regular
Joined
Oct 22, 2002
Messages
51
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
 

Forum statistics

Threads
1,144,274
Messages
5,723,444
Members
422,497
Latest member
dougy99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top