Getting Text From the Windows Clipboard

Posted on December 3, 2004 by Dick Kusleika

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

Sub FixDicksCode()

    Dim doClip As DataObject
    Dim sCode As String

    Set doClip = New DataObject

    doClip.GetFromClipboard
    sCode = doClip.GetText

    sCode = Replace(sCode, Chr$(148), Chr$(34))
    sCode = Replace(sCode, Chr$(147), Chr$(34))
    sCode = Replace(sCode, Chr$(145), Chr$(39))

    doClip.SetText sCode
    doClip.PutInClipboard

End Sub

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.

One thought on “Getting Text From the Windows Clipboard”

John Mansfield says:

December 3, 2004 at 9:03 am

Hi Dick,

I ran across the same problem and after a lot of research and frustration I finally resorted to using the PRE tag as a container for VB code. The PRE tag preserves all line spacing and basically allows a WYSIWYG look. It’s not perfect – for example, I would like to show comments in green just like the Excel VB editor does but I’ve not yet been able to figure out how to allow changes in font coloring to appear within the PRE container. Then, there’s possibly the indentation problem that you experienced when using my RSS feed – I’m still not sure what is happening there and if the PRE tag could somehow be the cause. However, in all other respects the PRE tag does the job well and also helps to keep the site compliant to XHTML standards. You can also apply formatting via your CSS template to the PRE tag. That’s how I’m adding the gray boxed look to all of my coding examples.

Some resources for the PRE tag and general design if you’re interested:

http://www.w3schools.com/tags/tag_pre.asp
http://www.mandarindesign.com/

By the way, I really like the format of your site – it looks great and the content, as always, is very interesting. I read it every day.

John Mansfield