Yesterday I discussed the DataObject object. In addition to putting text into the clipboard, you can also get text out of it. You use the GetFromClipboard and GetText methods of the DataObject object.
As you know, my new blogging software helpfully converts double quotes and apostrophes to their fancy counterparts. That makes copying the code to a module a real pain, particularly if I’ve done my job and used comments liberally. Until I get that fixed (and I am working on it), you could use the DataObject object to convert them back. To wit
Dim doClip As DataObject
Dim sCode As String
Set doClip = New DataObject
sCode = doClip.GetText
sCode = Replace(sCode, Chr$(148), Chr$(34))
sCode = Replace(sCode, Chr$(147), Chr$(34))
sCode = Replace(sCode, Chr$(145), Chr$(39))
Copy some screwed up code from my site, run this sub, then paste into a module. It takes the text from the clipboard and puts into doClip. Then, using GetText, puts the contents of doClip into a String variable. A couple of Replaces later (XL2000 and above), the text is fixed and is put back into the clipboard, ready to be pasted into a module.