none
A clipboard object for VBA, including Microsoft Word

    General discussion

  • I have been struggling with clipboard operations from Word VBA. I have spent dozens of hours Googling this, and although there are some partial examples that sort of work (sometimes), I was unable to find what I really needed. So I wrote it, and will post it below.

    Requirement: Polite macros. A macro that mangles the user's clipboard is rude. For example, your macro may do a Range.Copy. This copies the range into the Windows clipboard, clobbering whatever was there before. If your macro doesn't restore the clipboard, the user will discover, after running your macro, that a precious piece of text the user had copied to the clipboard earlier on has vanished. Bad macro. No biscuit.

    So the requirement is to preserve the clipboard contents before using the clipboard within the macro, then restore the clipboard contents afterwards. The DataObject is incapable of doing this -- and VBA does not provide native support to the Windows clipboard.

    I wrote a VBA class called vbaClipboard with a simple interface:

    vbaClipboard.ClipboardFormatsAvailable - returns a collection of ClipBoardFormat objects (each of which has a .Number and a .Name property). This tells you what formats are currently on the clipboard, returning both the CF_WHATEVER value (for built-in clipboard formats) and the custom format number for custom formats that have been registered (by whatever put the data on the clipboard).

    GetClipboardText(ByVal aClipboardFormatNumber As Long) As String - Returns the clipboard contents for the specified format number. If you aren't sure what formats are available, use the ClipboardFormatsAvailable property to find out.

    SetClipboardText(ByVal aText As String, ByVal aClipboardFormatName As String) - Stuffs aText into the clipboard using the specified clipboard format name. For built-in formats, use the CF_WHATEVER contsant name. E.g. SetClipboardText("Hello, world!", "CF_TEXT") For Rich Text Format, use SetClipboardText(sMyRTFTextString, "Rich Text Format"). It is important to use the same format name that Word uses when it manipulates the clipboard. "Rich Text Format" is correct for Word 2007 and 2010, and probably all earlier versions.

    The Test macros in the following code block illustrate how to use the vbaClipboard object.

    I have built on the work of several others whose names I unfortunately did not record. But thank you all.

     Comments? Improvements?

    In your VBA project, insert a new class called vbaClipboard, and paste in the following:

    Option Explicit
    
    
    
    Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    
    Private Declare Function CloseClipboard Lib "user32" () As Long
    
    Private Declare Function EmptyClipboard Lib "user32" () As Long
    
    Private Declare Function EnumClipboardFormats Lib "user32" (ByVal wFormat As Long) As Long
    
    Private Declare Function GetClipboardFormatName Lib "user32" Alias "GetClipboardFormatNameA" (ByVal wFormat As Long, ByVal lpString As String, ByVal nMaxCount As Long) As Long
    
    Private Declare Function RegisterClipboardFormat Lib "user32" Alias "RegisterClipboardFormatA" (ByVal lpString As String) As Long
    
    'Note that we do not use the GetClipboardDataA declaration
    
    'Public Declare Function GetClipboardData Lib "user32" Alias "GetClipboardDataA" (ByVal wFormat As Long) As Long
    
    Private Declare Function GetClipBoardData Lib "user32" Alias "GetClipboardData" (ByVal wFormat As Long) As Long
    
    Private Declare Function SetClipboardData Lib "user32" (ByVal wFormat As Long, ByVal hMem As Long) As Long
    
    Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
    
    Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
    
    'NOTE: the lstrCpy declaration you get from the VB6 API Viewer is WRONG. It's version is this:
    
    'Private Declare Function lstrcpy Lib "kernel32" Alias "lstrcpyA" (ByVal lpString1 As String, ByVal lpString2 As String) As Long
    
    'The correct version for (at least for Windows 7 / 64-bit is this:
    
    Private Declare Function lstrCpy Lib "kernel32" Alias "lstrcpyA" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
    
    Private Declare Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Long) As Long
    
    Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
    
    Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
    
    Private Declare Function GlobalFree Lib "kernel32" (ByVal hMem As Long) As Long
    
    
    
    Private Const GMEM_MOVEABLE = &H2
    
    Private Const GMEM_ZEROINIT = &H40
    
    Private Const GHND = (GMEM_MOVEABLE Or GMEM_ZEROINIT) 'Use for hwnd
    
    Private Const NAME_MAX_LENGTH = 1024
    
    
    
    Private Const APINULL = 0
    
    Private Const CF_TEXT = 1 'Text format. Each line ends with a carriage return/linefeed (CR-LF) combination. A null character signals the end of the data. Use this format for ANSI text.
    
    Private Const CF_BITMAP = 2 'A handle to a bitmap (HBITMAP).
    
    Private Const CF_METAFILEPICT = 3 'Handle to a metafile picture format as defined by the METAFILEPICT structure. When passing a CF_METAFILEPICT handle by means of DDE, the application responsible for deleting hMem should also free the metafile referred to by the CF_METAFILEPICT handle.
    
    Private Const CF_SYLK = 4 'Microsoft Symbolic Link (SYLK) format.
    
    Private Const CF_TIFF = 6 'Tagged-image file format.
    
    Private Const CF_DIF = 5 'Software Arts' Data Interchange Format.
    
    Private Const CF_OEMTEXT = 7 'Text format containing characters in the OEM character set. Each line ends with a carriage return/linefeed (CR-LF) combination. A null character signals the end of the data.
    
    Private Const CF_DIB = 8 'A memory object containing a BITMAPINFO structure followed by the bitmap bits.
    
    Private Const CF_PALETTE = 9 'Handle to a color palette. Whenever an application places data in the clipboard that depends on or assumes a color palette, it should place the palette on the clipboard as well.
    
    Private Const CF_PENDATA = 10 'Data for the pen extensions to the Microsoft Windows for Pen Computing.
    
    Private Const CF_RIFF = 11 'Represents audio data more complex than can be represented in a CF_WAVE standard wave format.
    
    Private Const CF_WAVE = 12 'Represents audio data in one of the standard wave formats, such as 11 kHz or 22 kHz PCM.
    
    Private Const CF_UNICODETEXT = 13 'Unicode text format. Each line ends with a carriage return/linefeed (CR-LF) combination. A null character signals the end of the data.
    
    Private Const CF_ENHMETAFILE = 14 'A handle to an enhanced metafile (HENHMETAFILE).
    
    Private Const CF_HDROP = 15  'A handle to type HDROP that identifies a list of files. An application can retrieve information about the files by passing the handle to the DragQueryFile function.
    
    Private Const CF_LOCALE = 16 'The data is a handle to the locale identifier associated with text in the clipboard. When you close the clipboard, if it contains CF_TEXT data but no CF_LOCALE data, the system automatically sets the CF_LOCALE format to the current input language. You can use the CF_LOCALE format to associate a different locale with the clipboard text.
    
    Private Const CF_DIBV5 = 17 'A memory object containing a BITMAPV5HEADER structure followed by the bitmap color space information and the bitmap bits.
    
    Private Const CF_DSPBITMAP = &H82 'Bitmap display format associated with a private format. The hMem parameter must be a handle to data that can be displayed in bitmap format in lieu of the privately formatted data.
    
    Private Const CF_DSPENHMETAFILE = &H8E  'Enhanced metafile display format associated with a private format. The hMem parameter must be a handle to data that can be displayed in enhanced metafile format in lieu of the privately formatted data.
    
    Private Const CF_DSPMETAFILEPICT = &H83 'Metafile-picture display format associated with a private format. The hMem parameter must be a handle to data that can be displayed in metafile-picture format in lieu of the privately formatted data.
    
    Private Const CF_DSPTEXT = &H81 'Text display format associated with a private format. The hMem parameter must be a handle to data that can be displayed in text format in lieu of the privately formatted data.
    
    Private Const CF_GDIOBJFIRST = &H300 'Start of a range of integer values for application-defined GDI object clipboard formats. The end of the range is CF_GDIOBJLAST.
    
    Private Const CF_GDIOBJLAST = &H3FF 'See CF_GDIOBJFIRST.
    
    Private Const CF_OWNERDISPLAY = &H80 'Owner-display format. The clipboard owner must display and update the clipboard viewer window, and receive the WM_ASKCBFORMATNAME, WM_HSCROLLCLIPBOARD, WM_PAINTCLIPBOARD, WM_SIZECLIPBOARD, and WM_VSCROLLCLIPBOARD messages. The hMem parameter must be NULL.
    
    Private Const CF_PRIVATEFIRST = &H200 'Start of a range of integer values for private clipboard formats. The range ends with CF_PRIVATELAST. Handles associated with private clipboard formats are not freed automatically; the clipboard owner must free such handles, typically in response to the WM_DESTROYCLIPBOARD message.
    
    Private Const CF_PRIVATELAST = &H2FF 'See CF_PRIVATEFIRST.
    
    
    
    Public Property Get ClipboardFormatsAvailable() As Collection
    
     On Error GoTo ErrorHandler
    
     Dim thisClipboardFormat As Long
    
     Dim returnStringLength As Long
    
     Dim myCFAvailable As New Collection
    
     Dim clipBoardFormatName As String
    
     Dim clipboardFormat As clipboardFormat
    
     Dim success As Boolean
    
     success = OpenClipboard(0)
    
     If success Then
    
      thisClipboardFormat = 0
    
      thisClipboardFormat = EnumClipboardFormats(thisClipboardFormat)
    
      While thisClipboardFormat <> 0
    
       Set clipboardFormat = New clipboardFormat
    
       clipBoardFormatName = String$(NAME_MAX_LENGTH, vbNullChar)
    
       returnStringLength = GetClipboardFormatName(thisClipboardFormat, _
    
        clipBoardFormatName, Len(clipBoardFormatName))
    
       clipBoardFormatName = TrimNull(clipBoardFormatName)
    
       If clipBoardFormatName = "" Then
    
        clipBoardFormatName = BuiltInClipboardFormatName(thisClipboardFormat)
    
       End If
    
       clipboardFormat.Name = clipBoardFormatName
    
       clipboardFormat.Number = thisClipboardFormat
    
       myCFAvailable.Add clipboardFormat, clipboardFormat.Name
    
       thisClipboardFormat = EnumClipboardFormats(thisClipboardFormat)
    
      Wend
    
      Set ClipboardFormatsAvailable = myCFAvailable
    
      CloseClipboard
    
     Else
    
      Set ClipboardFormatsAvailable = Nothing
    
     End If
    
     
    
     Exit Property
    
    ErrorHandler:
    
     On Error Resume Next
    
     CloseClipboard
    
    End Property
    
    
    
    Public Function GetClipboardText(ByVal aClipboardFormatNumber As Long) As String
    
     'Do not handle errors - let them bubble up
    
     Dim wLen As Integer
    
     Dim hMemory As Long
    
     Dim hMyMemory As Long
    
    
    
     Dim lpMemory As Long
    
     Dim lpMyMemory As Long
    
    
    
     Dim RetVal As Variant
    
     Dim haveMemoryLocked As Boolean
    
     Dim wClipAvail As Integer
    
     Dim szText As String
    
     Dim wSize As Long
    
     
    
     
    
     Dim clipBoardText As String
    
     clipBoardText = ""
    
     
    
     'Before accessing the clipboard, find out if the requested format is available
    
     If IsClipboardFormatAvailable(aClipboardFormatNumber) = APINULL Then
    
      Err.Raise vbObjectError + 1, "vbaClipboard", "Requested clipboard format number " & aClipboardFormatNumber & " is not available on the clipboard."
    
      Exit Function
    
     End If
    
     
    
     
    
     Dim success As Boolean
    
     success = OpenClipboard(0)
    
     If success Then
    
      'Get a handle to a memory structure containing the clipboard data in the requested format
    
      hMemory = GetClipBoardData(aClipboardFormatNumber)
    
      CloseClipboard
    
      'If the handle is null, something went wrong
    
      If hMemory = APINULL Then
    
       'Throw an error
    
       Err.Raise vbObjectError + 1, "vbaClipboard", "Unable to retrieve data from the Clipboard."
    
      End If
    
      'The handle is good. How much data came back?
    
      wSize = GlobalSize(hMemory)
    
      'Fill our destination string with nulls
    
      clipBoardText = Space(wSize)
    
    
    
      'Lock the memory
    
      'Get a pointer to the locked memory area
    
      lpMemory = GlobalLock(hMemory)
    
      If lpMemory = APINULL Then
    
       'CloseClipboard
    
       Err.Raise vbObjectError + 1, "vbaClipboard", "Unable to lock clipboard memory."
    
      End If
    
     
    
      ' Copy the locked memory into our string
    
      RetVal = lstrCpy(clipBoardText, lpMemory)
    
      
    
      'Unlock memory
    
      GlobalUnlock hMemory
    
      
    
      ' Get rid of trailing stuff.
    
      clipBoardText = Trim(clipBoardText)
    
      GetClipboardText = TrimNull(clipBoardText)
    
     Else
    
      Err.Raise vbObjectError + 1, "vbaClipboard", "Unable to open Clipboard. Perhaps some other application is using it."
    
     End If
    
    End Function
    
    
    
    Public Sub SetClipboardText(ByVal aText As String, ByVal aClipboardFormatName As String)
    
      
    
     Dim wLen As Integer
    
     Dim hMemory As Long
    
     Dim lpMemory As Long
    
     Dim RetVal As Variant
    
     Dim memoryIsLocked As Boolean
    
     Dim memoryIsAllocated As Boolean
    
     Dim clipBoardIsOpen As Boolean
    
     
    
     memoryIsAllocated = False
    
     memoryIsLocked = False
    
     clipBoardIsOpen = False
    
     
    
     On Error GoTo ErrorHandler
    
     
    
     ' Get the length, including one extra for a CHR$(0) at the end.
    
     wLen = Len(aText) + 1
    
     'Add a null to the end
    
     aText = aText & Chr$(0)
    
     'Allocate some memory
    
     hMemory = GlobalAlloc(GHND, wLen + 1)
    
     If hMemory = APINULL Then
    
      Err.Raise vbObjectError + 1001, "vbaClipboard", "Unable to allocate memory."
    
     Else
    
      memoryIsAllocated = True
    
     End If
    
     
    
     lpMemory = GlobalLock(hMemory)
    
     If lpMemory = APINULL Then
    
      'Throw an error
    
      Err.Raise vbObjectError + 1001, "vbaClipboard", "Unable to lock memory."
    
     Else
    
      memoryIsLocked = True
    
     End If
    
    
    
     ' Copy our string into the locked memory.
    
     RetVal = lstrCpy(lpMemory, aText)
    
     ' Don't send clipboard locked memory.
    
     RetVal = GlobalUnlock(hMemory)
    
     'If the preceding throws an error, it will be handled in ErrorHandler
    
     memoryIsLocked = True
    
     If OpenClipboard(0&) = APINULL Then
    
      Err.Raise vbObjectError + 1, "vbaClipboard", "Unable to open Clipboard. Perhaps some other application is using it."
    
     Else
    
      clipBoardIsOpen = True
    
     End If
    
     
    
     
    
     'Is the requested format one of the Windows built-in formats?
    
     Dim i As Integer
    
     Dim thisClipboardFormatNumber As Long
    
     thisClipboardFormatNumber = BuiltInClipboardFormatNumber(aClipboardFormatName)
    
     If thisClipboardFormatNumber = 0 Then
    
      'Nope. Register the format
    
      On Error Resume Next
    
      thisClipboardFormatNumber = RegisterClipboardFormat(aClipboardFormatName)
    
      If Err.Number <> 0 Then
    
       Err.Raise vbObjectError + 1, "vbaClipboard", "Unable to register clipboard format: " & aClipboardFormatName & _
    
        ". Error message: " & Err.description
    
      End If
    
      
    
      On Error GoTo ErrorHandler
    
      If thisClipboardFormatNumber = 0 Then
    
       Err.Raise vbObjectError + 1, "vbaClipboard", "Unable to register clipboard format: " & aClipboardFormatName
    
      End If
    
     End If
    
     
    
     'Empty the clipboard
    
     If EmptyClipboard() = APINULL Then
    
      Err.Raise vbObjectError + 1, "vbaClipboard", "Unable to empty the clipboard."
    
     End If
    
     
    
     If SetClipboardData(thisClipboardFormatNumber, hMemory) = APINULL Then
    
      Err.Raise vbObjectError + 1, "vbaClipboard", "Unable to set the clipboard data."
    
     End If
    
     
    
     CloseClipboard
    
     GlobalFree hMemory
    
     
    
     Exit Sub
    
    ErrorHandler:
    
     Dim description As String
    
     description = Err.description
    
     On Error Resume Next
    
     If memoryIsLocked Then GlobalUnlock hMemory
    
     If memoryIsAllocated Then GlobalFree hMemory
    
     If clipBoardIsOpen Then CloseClipboard
    
     On Error GoTo 0
    
     Err.Raise vbObjectError + 1, "vbaClipboard", description
    
    End Sub
    
    
    
    
    
    Private Function TrimNull(ByVal aString As String) As String
    
     TrimNull = Left(aString, _
    
       InStr(1, aString, vbNullChar) - 1)
    
    End Function
    
    
    
    Private Function BuiltInClipboardFormatNumber(ByVal aClipboardFormatName As String) As Long
    
     Dim result As Long
    
     Select Case UCase(aClipboardFormatName)
    
      Case "CF_TEXT"
    
       result = 1
    
      Case "CF_BITMAP"
    
       result = 2
    
      Case "CF_METAFILEPICT"
    
       result = 3
    
      Case "CF_SYLK"
    
       result = 4
    
      Case "CF_DIF"
    
       result = 5
    
      Case "CF_TIFF"
    
       result = 6
    
      Case "CF_OEMTEXT"
    
       result = 7
    
      Case "CF_DIB"
    
       result = 8
    
      Case "CF_PALETTE"
    
       result = 9
    
      Case "CF_PENDATA"
    
       result = 10
    
      Case "CF_RIFF"
    
       result = 11
    
      Case "CF_WAVE"
    
       result = 12
    
      Case "CF_UNICODETEXT"
    
       result = 13
    
      Case "CF_ENHMETAFILE"
    
       result = 14
    
      Case "CF_HDROP"
    
       result = 15
    
      Case "CF_LOCALE"
    
       result = 16
    
      Case "CF_DIBV5"
    
       result = 17
    
      Case "CF_DSPBITMAP"
    
       result = &H82
    
      Case "CF_DSPENHMETAFILE"
    
       result = &H8E
    
      Case "CF_DSPMETAFILEPICT"
    
       result = &H83
    
      Case "CF_DSPTEXT"
    
       result = &H81
    
      Case "CF_GDIOBJFIRST"
    
       result = &H300
    
      Case "CF_GDIOBJLAST"
    
       result = &H3FF
    
      Case "CF_OWNERDISPLAY"
    
       result = &H80
    
      Case "CF_PRIVATEFIRST"
    
       result = &H200
    
      Case "CF_PRIVATELAST"
    
       result = &H2FF
    
      Case Else
    
       result = 0
    
     End Select
    
      BuiltInClipboardFormatNumber = result
    
    End Function
    
    
    
    Private Function BuiltInClipboardFormatName(ByVal aIndex As Integer) As String
    
     Dim n As String
    
     Select Case aIndex
    
      Case 1
    
       n = "CF_TEXT"
    
      Case 2
    
       n = "CF_BITMAP"
    
      Case 3
    
       n = "CF_METAFILEPICT"
    
      Case 4
    
       n = "CF_SYLK"
    
      Case 5
    
       n = "CF_DIF"
    
      Case 6
    
       n = "CF_TIFF"
    
      Case 7
    
       n = "CF_OEMTEXT"
    
      Case 8
    
       n = "CF_DIB"
    
      Case 9
    
       n = "CF_PALETTE"
    
      Case 10
    
       n = "CF_PENDATA"
    
      Case 11
    
       n = "CF_RIFF"
    
      Case 12
    
       n = "CF_WAVE"
    
      Case 13
    
       n = "CF_UNICODETEXT"
    
      Case 14
    
       n = "CF_ENHMETAFILE"
    
      Case 15
    
       n = "CF_HDROP"
    
      Case 16
    
       n = "CF_LOCALE"
    
      Case 17
    
       n = "CF_DIBV5"
    
      Case &H82
    
       n = "CF_DSPBITMAP"
    
      Case &H8E
    
       n = "CF_DSPENHMETAFILE"
    
      Case &H83
    
       n = "CF_DSPMETAFILEPICT"
    
      Case &H81
    
       n = "CF_DSPTEXT"
    
      Case &H300
    
       n = "CF_GDIOBJFIRST"
    
      Case &H3FF
    
       n = "CF_GDIOBJLAST"
    
      Case &H80
    
       n = "CF_OWNERDISPLAY"
    
      Case &H200
    
       n = "CF_PRIVATEFIRST"
    
      Case &H2FF
    
       n = "CF_PRIVATELAST"
    
     End Select
    
     BuiltInClipboardFormatName = n
    
    End Function
    
    
    
    

    Insert another class in your project, and call it ClipboardFormat. Paste in the following:

    Option Explicit
    
    Private mNumber As Long
    Private mName As String
    
    Public Property Get Number() As Long
      Number = mNumber
    End Property
    Public Property Let Number(ByVal value As Long)
      mNumber = value
    End Property
    
    Public Property Get Name() As String
      Name = mName
    End Property
    Public Property Let Name(ByVal value As String)
      mName = value
    End Property
    

     Try these test macros:

    Option Explicit
    Sub test()
        'This routine tests the vbaClipboard object.
        'Before running this, copy some text from Word. This will place Rich Text Format data
        'on the clipboard. The test will preserve the RTF data, then use the clipboard
        'to manipulate some plain text ("CF_TEXT"). Finally, the test will put the
        'RTF data back on the clipboard. When the test is finished, you should be able
        'to go back into Word and hit Ctrl+V and paste your original copied text (with formatting).
       
        'Instantiate a vbaClipboard object
        Dim myClipboard As New vbaClipboard
       
        'The ClipboardFormat class encapsulates a clipboard format number and a name
        Dim clipboardFormat As clipboardFormat
       
        'Handle errors below
        On Error GoTo ErrorHandler
       
        'Show the currently available formats
        'The ClipboardFormatsAvailable property returns a collection of ClipboardFormat objects
        'representing all formats currently available on the clipboard.
       
        Debug.Print "===================================================================="
       
        For Each clipboardFormat In myClipboard.ClipboardFormatsAvailable
            Debug.Print clipboardFormat.Number, clipboardFormat.Name
        Next clipboardFormat
           
        'Preserve the RTF currently on the clipboard (you did copy some, right?)
        Dim oldRTF As String
        'Get the format number value for Rich Text Format
        Dim richTextFormatNumber As Long
        On Error Resume Next
        richTextFormatNumber = myClipboard.ClipboardFormatsAvailable("Rich Text Format").Number
        If Err.Number <> 0 Then
            On Error GoTo ErrorHandler
            Err.Raise vbObjectError + 1, , "The clipboard does not have any Rich Text Format data."
        End If
        On Error GoTo ErrorHandler
       
        'Get the RTF data from the clipboard
        oldRTF = myClipboard.GetClipboardText(richTextFormatNumber)
        'Debug.Print oldRTF
       
        'Use the clipboard for something else
        Dim s As String
        s = "Hello, world!"
        myClipboard.SetClipboardText s, "CF_TEXT"
       
        'Get it back again
        Debug.Print myClipboard.GetClipboardText(1)
       
        'Show the currently available formats
        Debug.Print "===================================================================="
        For Each clipboardFormat In myClipboard.ClipboardFormatsAvailable
            Debug.Print clipboardFormat.Number, clipboardFormat.Name
        Next clipboardFormat
       
        'Now put back the RTF
        myClipboard.SetClipboardText oldRTF, "Rich Text Format"
       
        'Show the currently available formats
        Debug.Print "===================================================================="
        For Each clipboardFormat In myClipboard.ClipboardFormatsAvailable
            Debug.Print clipboardFormat.Number, clipboardFormat.Name
        Next clipboardFormat
        'You can now paste back into Word, and you'll get whatever text you selected
        Exit Sub
    ErrorHandler:
        MsgBox Err.description
    End Sub
    Sub test2()
        'This tests stuffs some formatted text (RTF) onto the clipboard. Run the test, then
        'go into word and hit Ctrl+V to paste it in.
        Dim myClipboard As New vbaClipboard
        Dim sRTF As String
        sRTF = "{\rtf1\ansi\ansicpg1252\deff0\deftab720{\fonttbl" & _
               "{\f0\fswiss MS Sans Serif;}{\f1\froman\fcharset2 Symbol;}" & _
               "{\f2\froman\fprq2 Times New Roman;}}" & _
               "{\colortbl\red0\green0\blue0;\red255\green0\blue0;}" & _
               "\deflang1033\horzdoc{\*\fchars }{\*\lchars }" & _
               "\pard\plain\f2\fs24 This is some \plain\f2\fs24\cf1" & _
               "formatted\plain\f2\fs24  text.\par }"
                 
        myClipboard.SetClipboardText sRTF, "Rich Text Format"
    End Sub
    Monday, July 26, 2010 5:12 PM

All replies

  • Ouch. In my description of the vbaClipboard object interface, I made two mistakes:

    • It's not myClipboard.ClipboardFormatsAvailable, of course. it's vbaClipboard.ClipboardFormatsAvailable
    •  use SetClipboardText("Hello, world!", "Rich Text Format"). This won't work, because "Hello, world!" is not RTF-encoded. You would use this method after preserving some RTF you collected (or composed) elsewhere. The Test macros illustrate how to use this correctly.

     

    Monday, July 26, 2010 5:18 PM
  • Ignore that. I edited my original post and fixed it.
    Monday, July 26, 2010 5:25 PM
  • Hi L (Leigh, is that you? Long time no "see"!)

    This is great - thanks so much for posting it!

    One bit of information to supplement this, as I expect it's going to be around a long time and become a very useful resource. As you say, it's best to avoid copying to the Clipboard if possible.

    When information should be transferred between two Word documents, except in special circumstances, the Range.FormattedText property will do the job, without needing the Clipboard:

    myTargetRange.FormattedText = mySourceRange.FormattedText


    Cindy Meister, VSTO/Word MVP
    Monday, July 26, 2010 6:37 PM
  • wdDoc.Range.formattedText is okay for plain text only when copying between Word and Excel. For example, the following piece of code does NOT preserve forrmatting when copying the data from a Word document to an Excel cell, despite the fact an Excel cell is RTF compatible. Nor can the .characters property of FormattedText be copied to the .Value, .Text or .Characters properties of an Excel cell. Additionally, the .copy method in Word does not have a Destination:= option (this option in the Excel range object copy method will copy the formatting from one Excel cell to another).

    Matters.Range("db_Matters_Field_Com").Cells(dbMattersIdx, 1) = wdDoc.Range.FormattedText

     

     

     


    phillfri
    • Edited by phillfri Tuesday, January 03, 2012 5:14 AM
    Tuesday, January 03, 2012 5:13 AM
  • Hi Phil

    I did say specifically that Range.FormattedText works between two Word documents...

    Word doesn't use RTF internally, so it doesn't matter whether another application supports RTF or not. When putting information on the Clipboard Word will convert its internal format to RTF and HTML for this reason.


    Cindy Meister, VSTO/Word MVP
    Tuesday, January 03, 2012 8:19 AM
  • Cindy

    I there any another way to get the RTF text from a document without using clipboard?

    I agree with you and L the customer is not amused about lose any information he copied into the windows clipboard. We are working now since 15 years with Word but could not find any another reliable way to get the RTF text of a selection.

    There was a new method introduced with Word 2007 called ExportFragment but this was again broken with Word 2010. See http://social.msdn.microsoft.com/Forums/da-DK/worddev/thread/283e6274-0fe7-439d-ba72-ee3f5faf104b

    Why can Microsoft not provide a method to read formatted content of a selection or range?

    Kind regards


    Paul
    Wednesday, January 04, 2012 7:11 PM
  • Hi Paul

    <<Why can Microsoft not provide a method to read formatted content of a selection or range?>>

    Well, in Word 2010 you would have the option of picking up the WordOpenXML directly out of the document, as a string. This certainly provides you the content of any selection or range. The string can be "streamed" to pretty much anywhere. And it could theoretically be transformed into RTF if you absolutely need RTF.

    Other than that, transfer the content to a new document then SaveAs to the RTF file format.


    Cindy Meister, VSTO/Word MVP
    Wednesday, January 04, 2012 8:08 PM
  • Hello all,

    I found this thread trying to figure something out. 

    I am a newbie to VBA,  I will try to digest this thread.  I am using WORD. 

    Is it possible to write a Macro so that: while in a WORD document, select and copy one whole line of the document to use as the name of a new file to save?  This would involve getting the contents of the clipboard to be the new filename. 

    The line I want to use as the new filename might be someone's name of any length.  Or the line might be an ID containing numerals and letters together. 

    Thanks, - Steve from Kansas

    Also, what book can you recommend I get that will teach EVERYTHING about VBA for Office 2010? 


    Stephen.Andre

    Thursday, February 09, 2012 2:47 PM
  • Hi Stephen

    As this thread is a comment, not a question, and older, I recommend you post your question in a new thread. If you feel information in this thread is relevant, you can insert a link to this thread in your new question.

    FWIW there is no book, anywhere, that can teach you everything about VBA for any version of Word. Word is so big, it would take thousands of pages to handle all aspects.

    But if you want recommendations, I recommend you ask this as a separate question, with its own subject line. Otherwise, no one is going to see your question, so you won't get any answers.


    Cindy Meister, VSTO/Word MVP

    Thursday, February 09, 2012 3:36 PM
  • You don't need the clipboard to do this. Selection.Range.Text will get you the plain text of whatever is selected. Your macro can then try to use it as the filename in the Document.Save method. I say "try" because the selected text may contain characters that are not allowed in file names, e.g. : ? / and others. You will need to scrub the text of those characters before attempting the save. Also, you should consider what to do about file name collisions (an existing document having the same name that you propose to use).

    Leigh Webber

    Thursday, February 09, 2012 3:41 PM
  • Thanks Cindy, I may post a seperate request for a Comprehensive Book.  But for right now, I am considering this:

    Mastering VBA for Office 2010 by Richard Mansfield. 

    Leigh, You sound like you know what you are doing.  I will go look up Selection.Range.Text  & Document.Save  

    And thanks for your warning about scrubbing the text.  But this will be no problem because it is just numerals and Alpha.  Collisions are also no problem because these numbers/filenames will be very unique. 

    So now I need to write a Dim statement to get a variable to move the selected text into and then use that as the file name.  Right?  I will get to work right away.  - Thanks, - Steve from Kansas


    Stephen.Andre

    Thursday, February 09, 2012 5:01 PM
  • Well Leigh, Here's my attempt.  As you can see, I don't know how to get the selected line into the filename because this does not work. 

    Selection.HomeKey Unit:=wdStory
        Selection.MoveDown Unit:=wdLine, Count:=2
        Selection.HomeKey Unit:=wdLine
        Selection.EndKey Unit:=wdLine, Extend:=wdExtend
        Dim MyRange As Object
        Set MyRange = Selection.Range
       
        ChangeFileOpenDirectory _
            "\\Computername99\users\stephen.Andre\Tickets\"
        ActiveDocument.SaveAs FileName:= _
            "\\Computername99\users\stephen.Andre\Tickets\Myrange.docx" _
            , FileFormat:=wdFormatXMLDocument, LockComments:=False, Password:="", _
            AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
            EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
            :=False, SaveAsAOCELetter:=False


    Stephen.Andre


    • Edited by StephenAndre Thursday, February 09, 2012 7:56 PM
    Thursday, February 09, 2012 7:55 PM
  • Yikes!

    Try this:

    Option Explicit

    Sub SaveToFileBasedOnSelection()
        Dim selectionText As String
        Dim fileName As String
        Dim filePathName As String
       
        'Change this constant to point to the directory where
        'you want saved documents to go. Omit the trailing /
        Const kSaveDirectory = "c:\temp"
       
        'Make sure there is some text selected
        If Selection.Type <> wdSelectionNormal Then
            MsgBox "You must select some document text before running this macro."
            Exit Sub
        End If
       
        'Get the selected text
        selectionText = Selection.Range.Text
           
        fileName = selectionText & ".docx"
        filePathName = kSaveDirectory & "\" & fileName
       
        'You better be REALLY sure that this is a valid file name, because here goes
        On Error Resume Next
        ActiveDocument.SaveAs2 fileName:=filePathName, FileFormat:=wdWord2010
        If Err.Number <> 0 Then
            MsgBox "Can't save the document with the name """ & filePathName & """. " & Err.Description
        End If
       
    End Sub


    Leigh Webber

    Thursday, February 09, 2012 8:18 PM
  • Thanks Leigh, I'll try it and get back with you tomorrow. It is almost time to go home for the day. 

    Yikes!  - Indeed.


    Stephen.Andre

    Thursday, February 09, 2012 8:47 PM
  • Leigh, I have been working with it.  Many Thanks to you,  I think one problem is that we still have VBA 6.0 here at work.  So I took off the FileFormat:=wdWord2010

    And I changed the Saveas2 to Saveas because Saveas2 did not work. 

    Now I get some kind of permissions error citing the original document name. 

    In the meantime, I got a good book and I will continue to learn. 

    This seems like such a basic straightforward task.  I used to be a whiz at COBOL.  This seems more difficult.  Ha! 

    Any other ideas for Word 2007 VBA, that is VBA 6.0   ????


    Stephen.Andre

    Friday, February 10, 2012 5:47 PM
  • There are very few differences in VBA between Word versions -- but the document types are different, as you discovered.

    As a novice, try the simplest possible macro first. Maybe one that simply saves the active document using its existing name. Then mod it to do a SaveAs. When that works, add the other functionality a bit at a time. The goal is to isolate the problems so that you know exactly what is causing them. You start with something that works, then add a single extra element at a time. When the macro breaks, you know it was whatever you just added.

    COBOL. 80-column punch cards. Long nights at the campus data center. Playing Star Trek on the Teletype terminals. Bet you saw Leave it to Beaver original episodes.  :-)


    Leigh Webber

    Friday, February 10, 2012 6:40 PM
  • Leigh,

    You sound just like me, old as dirt.  Yes, I did write FORTRAN programs on punch cards at UMKC.  And yes, I did play Star Trek on the Teletype terminals on campus.  That was back in the middle '70s.  But I wrote many COBOL programs here at work too.  And yes, I did see Leave It To Beaver original episodes back in the '60s.  That was one of the few television programs I was allowed to watch. 

    I have been away for a few days.   Please check back here every day or so because I want to get this figured out with your help.  Perhaps you can Email me.  I have VBA 7 at home and we expect to get Office 2010 with VBA 7 here at work this year.  Now we only have VBA 6. 

    I just got the book, "Mastering VBA for Microsoft Office 2010" by Richard Mansfield.  It is a help. 

    I will give you more details later, but I think it is trying to save the file with the variable but there is some kind of permissions error.  It said it "...could not complete the action." 

    Thanks for the advice about starting simple.  I will try that, but I have to get to work.  They expect me to produce something around here and this VBA stuff is just my idea to help me with some repetitive work. 

    Thanks


    Stephen.Andre


    Tuesday, February 14, 2012 2:41 PM
  • The forum emails me whenever a response is posted to this thread, so no worries. Also, I prefer to keep the discussion on the forum so that it will benefit others down the road.

    Good luck, and keep at it. VBA is very powerful -- not much you can't do with it.


    Leigh Webber

    Tuesday, February 14, 2012 5:01 PM
  • Yippeeeee!   Thanks!  It worked.  I made two changes. 

    This did not work:    "\\ComputerName\Name\SomeName\etc"
     
    Instead, This works:  "C:\Name\SomeName\etc"


    And, somebody told me to insert this code because of a permissions error. This code deletes the troublesome Paragraph Mark at the end of the line. 

    If InStr(strFileName, vbCr) <> 0 Then
       strFileName = Trim(Left(strFileName, InStr(strFileName, vbCr) - 1))
       End If


    So the entire code you helped me with is as follows.  This works:

    Selection.HomeKey Unit:=wdStory
        Selection.MoveDown Unit:=wdLine, Count:=2
        Selection.HomeKey Unit:=wdLine
        Selection.EndKey Unit:=wdLine, Extend:=wdExtend
        Selection.Copy
       
       
       Dim strPath As String
       Dim strFileName As String

       'set pathname accordingly
       strPath = "C:\Remedy Tickets\"

       'create the Filename with your selection in Document
       strFileName = Trim(Selection.Text) & ".docx"

       If InStr(strFileName, vbCr) <> 0 Then
       strFileName = Trim(Left(strFileName, InStr(strFileName, vbCr) - 1))
       End If

     

    ActiveDocument.SaveAs fileName:= _
             strPath & strFileName _
            , FileFormat:=wdFormatXMLDocument, LockComments:=False, Password:="", _
            AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
            EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
            :=False, SaveAsAOCELetter:=False
     


    Stephen.Andre

    Wednesday, February 22, 2012 12:54 PM
  • Glad you got it to work. One minor point: you don't need the Selection.Copy, because you're not using the clipboard at all. The Selection.Text yields the selected text -- no copying necessary.

    Leigh Webber

    Wednesday, February 22, 2012 8:33 PM
  • 

    Hi Leigh, thanks for your code. I was ecstatic, as I have been trying for ages to get a seemingly simple thing to work - I'd like to get at the underlying html of a clipboard copy, so when the user types formatted text in Word, I'd like to pick up the html via the clipboard, for example:  <div>This is a <font color=red>test </font>of <strong>formatted </strong>text.</div> . So I thought I could use your  code as follows:

    htmlTextFormatNumber = myClipboard.ClipboardFormatsAvailable("HTML Format").Number
    oldRTF = myClipboard.GetClipboardText(htmlTextFormatNumber)

    to get at that html. But even though htmlTextFormatNumber is valid (its value is 49375)

    oldRTF = myClipboard.GetClipboardText(htmlTextFormatNumber)

    fails with Invalid Procedure Call or Argument.

    Do you have any idea why that might be? And do you have any idea how I may be able to get at the HTML format of the clipboard? I've tried everything!

    Many thanks, Howard

    Monday, November 05, 2012 1:32 PM
  • There's a bug in my code. Here's the corrected piece:

    ''''''''''''''''''''''''''''''''''''''''''''''

    Private Function TrimNull(ByVal aString As String) As String
        Dim nullAt As Long
        nullAt = InStr(1, aString, vbNullChar)
        If nullAt > 0 Then
            TrimNull = Left(aString, _
                nullAt - 1)
        Else
            TrimNull = aString
        End If
    End Function

    ''''''''''''''''''''''''''''''''''''''


    Leigh Webber

    Tuesday, November 06, 2012 2:47 PM
  • Hello,

    in property ClipboardFormatsAvailable there is a declaration that does not compile:

    Dim clipboardFormat As clipboardFormat

    A workaround very welcome

    Pawel

    Sunday, March 03, 2013 1:48 PM
  • Make sure you have two modules: the first is a class module that should contain the vbaClipboard class. The second should be a plain module, and should contain the clipboardFormat code. Go back up to that looooong source code listing and make sure you have two separate modules, as described. Also, if you wrote your own test macro, make sure it is in a plain module, not inside a class module.

    Leigh Webber

    Friday, March 08, 2013 8:19 PM
  • Hello,

    The only working configuration for me was, eventually, when both vbaClipboard  and clipboardFormat are class modules and my own macros goes to the plain module.

    Pawel


    • Edited by klarak Wednesday, March 13, 2013 9:47 AM
    Monday, March 11, 2013 11:58 AM
  • Leigh,

    I am trying to get one one or more inlineshapes from a document, store them in an array and then use that array of stored inlineshapes to add (or paste or whatever) one or more inline shapes into another document.

    Sub SCRATCHMACRO() 
        Dim oDoc As Word.Document 
        Dim arrILS(0) As Variant, oILS As InlineShape 
         'Loading an InlineShape from one document into an array.
        Set arrILS(0) = ActiveDocument.InlineShapes(1) 
         'Trying to insert that array stored ILS into a new document.
        Set oDoc = Documents.Add 
         'Add the ILS
        Set oILS = ActiveDocument.InlineShapes.New(Selection.Range) 
         'How to insert the array stored ILS into this new document?
         'oILS = arrILS(0) ??????????????????
         'Thanks.
    End Sub 
    I can't make it work and stumbled on your code here looking for a way to put the arrary stored ILS in the clipboard and then pasting it in the new document. I know I can copy an ILS from one document to the clipboard and paste it in a new document using the clipboard, but I can't figure out how to put the ILS in the clipboard with VBA or by using your class either. Any ideas?  Thanks.

    Greg Maxey Please visit my website at: http://gregmaxey.mvps.org/word_tips.htm

    Wednesday, April 24, 2013 12:29 PM
  • I don't think you need my macro to do this. Simply copy the ILS range and paste it into the new document:

    Sub SCRATCHMACRO()
        Dim oDoc As Word.Document
        ActiveDocument.InlineShapes(1).Range.Copy
        Set oDoc = Documents.Add
        oDoc.Range.Paste
    End Sub


    Leigh Webber

    Wednesday, April 24, 2013 2:16 PM
  • Leigh,

    True.  To do what the simple example macro does I could copy and paste.  It was just to illustrate that I have a collection of inlineshapes stored in an array.  I then need to put one or more of those shapes in one or more other documents.  I can put the shapes from the various documents in the array, I just can't find a way to add a shape to a document from the array.  So I was hoping I could somehow put the shape in the clipboard and then use the clipboard to paste in the open document.


    Greg Maxey Please visit my website at: http://gregmaxey.mvps.org/word_tips.htm

    Wednesday, April 24, 2013 3:17 PM
  • I'm sticking to my guns. Here's some pseudo-code

    Dim i as integer
    i = 0
    for each myInlineShape in ActiveDocument.InlineShapes
        i = i + 1
        Redim preserve myArray(i)
        myArray(i) = myInlineShape.Range  'store the range object in the array
    next myInlineShape

    Dim j as integer
    for j = 1 to i
        set myNewDoc = Documents.Add
        myArray(i).Copy  'since the array holds ranges, you can .Copy them to the cb
        myNewDoc.Range.Paste
    next j

    None of this tested, of course.


    Leigh Webber

    Wednesday, April 24, 2013 4:03 PM
  • Leigh,

    That didn't work, but this does:

    Sub ScratchMacro()
    Dim lngIndex As Long
    Dim oILS As InlineShape
    Dim arrILS() As Variant
    Dim oDoc As Word.Document
    lngIndex = 0
    For Each oILS In ActiveDocument.InlineShapes
      ReDim Preserve arrILS(lngIndex)
      Set arrILS(lngIndex) = oILS
      lngIndex = lngIndex + 1
    Next oILS
    For lngIndex = LBound(arrILS) To UBound(arrILS)
      Set oDoc = Documents.Add
      arrILS(lngIndex).Range.Copy
      oDoc.Range.Paste
    Next lngIndex
    lbl_Exit:
      Exit Sub
    End Sub

    Thanks. Now, your class has a SetClipboardText property (or method, I just not sure of the terminology) that takes a string argument.  Do you think it would be possible to modify your class to take an object (i.e., SetClipboardObject  and pass an object variable e.g, oILS as String).

    Thanks again.

     

    Greg Maxey Please visit my website at: http://gregmaxey.mvps.org/word_tips.htm

    Wednesday, April 24, 2013 4:25 PM
  • Just a thought.

    AFAICS the problems are that

     a. what you are storing is a VBA InlineShape object, which is more like a reference to an InlineShape in an existing document than a complete InlineShape that could be inserted in a document.

     b. there is no call that says "insert the InlineShape object rferenced by this VBA object in another document"

    But if you are in a position to get the range of each InlineShape, you could perhaps store the XML of the range, and use InsertXML to insert that in the target document - at its simolest, in Word 2003 VBA, something like

    Sub copyInlineShapeToNewDocument()
    Dim d As Document

    Dim r as Range

    Dim s As String

    s = Selection.Range.XML
    Set d = Documents.Add
    d.Content.InsertXML s
    End Sub


    Peter Jamieson

    Wednesday, April 24, 2013 7:06 PM
  • Or another possibility might be to create a link field to the original shape (assuming it is still available), then replace the link field by its result in the usual way. 

    Peter Jamieson

    Wednesday, April 24, 2013 7:38 PM
  • The challenge will be to get the binary data representing the shape itself. The Windows API clipboard methods need a reference to a block of memory filled with the data to be placed on the clipboard. To create that block of memory, you have to know how big to make it. You then have to populate it with data.

    The problem is that Word VBA does not expose these properties of an InLineShape (or a Shape, for that matter). But if you copy an ILS.Range to the clipboard, you can then use my vbaClipboard object to examine what's in there. I tried this with a simply Alt+PrtScr screen grab graphic pasted into Word. Here are the clipboard formats:

    49161        DataObject
     49841        Art::GVML ClipFormat
     2            CF_BITMAP
     49699        PNG
     49737        JFIF
     49734        GIF
     14           CF_ENHMETAFILE
     3            CF_METAFILEPICT
     49166        Object Descriptor
     49171        Ole Private Data
     8            CF_DIB
     17           CF_DIBV5

    Those look promising. You could probably use the Windows API to pull the binary data out of the clipboard for whichever of those formats you want. This would get you a memory structure containing the binary data. You could convert that to a byte array and store it in VBA any way you want. To shove it back into the clipboard, you would use the complementary Win API calls. The existing methods in my vbaClipboard class are well commented, and you should be able to use them as a starting point to write the additional methods.

    But I still don't understand why you would want to go this route. The simple VBA I suggested (and you perfected) does the job. Why isn't it good enough?

    BTW: "Property" = an attribute of an object, e.g. your body weight.

    "Method" = a set of steps that perform some action on an object, e.g. eating Big Macs.


    Leigh Webber

    Wednesday, April 24, 2013 9:20 PM
  • Ooh, now that is clever. The ILS.Range.XML property returns a complete binary encoding of the graphic itself. You could just store that big xml string in vba somewhere, and do whatever you want with it. You could store it in a database, or into a text file. Then later you could fetch that string and use the Range.InsertXML method to plop it into a document.

    Sehr kuhl.


    Leigh Webber

    Wednesday, April 24, 2013 9:29 PM
  • Peter,

    Yes that is sweet.  No need to monkey with the clipboard at all.  Thanks:

    Sub ScratchMacro()
    Dim lngIndex As Long
    Dim oILS As InlineShape
    Dim arrILS() As Variant
    Dim oDoc As Word.Document
    Dim strXML As String
    lngIndex = 0
    For Each oILS In ActiveDocument.InlineShapes
      ReDim Preserve arrILS(lngIndex)
      Set arrILS(lngIndex) = oILS
      lngIndex = lngIndex + 1
    Next oILS
    For lngIndex = LBound(arrILS) To UBound(arrILS)
      Set oDoc = Documents.Add
      strXML = arrILS(lngIndex).Range.XML
      oDoc.Range.InsertXML strXML
    Next lngIndex
    lbl_Exit:
      Exit Sub
    End Sub


    Greg Maxey Please visit my website at: http://gregmaxey.mvps.org/word_tips.htm

    Wednesday, April 24, 2013 10:23 PM
  • Leigh,

    Because Peter's suggestion is better ;-).

    No just kidding.  It was good enough.  I hadn't played with your code that much but if I understand your decripiton then whatever was in the clipboard was preserved after using your class.  If I used my code based on your suggestion wouldn't the existing CB content be wiped?

    Thanks for all your help and prodding.


    Greg Maxey Please visit my website at: http://gregmaxey.mvps.org/word_tips.htm

    Wednesday, April 24, 2013 10:25 PM
  • Glad it helped, but bear in mind that inlineshape objects could be very large!

    Peter Jamieson

    Thursday, April 25, 2013 6:54 AM
  • I wish this code ran under 64-bit Office.  I cannot get 'Function GlobalLock Lib "kernel32"' to work when declared as PtrSafe...
    Wednesday, August 21, 2013 8:23 PM
  • Absolutely awesome, thank you so much for this! :-)
    Tuesday, August 27, 2013 8:19 PM
  • Leigh

    I'm trying to make your code work to transfer formatted text between Access (2010) and Word (2010). The initial text in Access was not RTF, but was crudely formatted many years ago with spaces using a fixed-width font. 

    I want to copy that field,( which is defined as a Memo field with the format property set to RTF ) into Word, where the user will introduce tabs and other basic formatting. Then I need to select the entire range and copy it back to the field in Access. This process may be repeated several times, but clearly, after the first time, there will actually be RTF tags where there weren't any before.

    The process is done by setting the focus on the Access field, copying it, and pasting it into Word. The trouble is that once the real RTF tags are present, it pastes into Word displaying the tags. This occurs no matter what clipboard format is specified. When I go the other way (from Word to Access) it makes a complete jumble out of the text.

    Any thoughts on why this process fails? Any thoughts on alternatives? 

    (By the way, I met you several times via the now-defunct ICCA and ,I think , Will Rico. I'm another one of those "older than dirt" people who goes back as far as the late '60's, Fortran, and aerospace. Best regards.)

    John

    Tuesday, October 29, 2013 6:14 PM
  • Are you doing the copy and paste in code, or via the UI? If the latter, I assume you have tried the various "Paste As..." options.

    As a general troubleshooting suggestion, I would use the VBA to copy from the Access field, then dump the contents into Notepad and have a look at it. Start with an empty field, then try putting a single vanilla character into the field, then try with various formatting attributes. Then do the same with Word, and compare the raw RTF with the raw RTF that Access uses. That should point out any differences, and may lead you to a better understanding of what is actually going on -- or lead to a workaround where you perhaps do some simply manipulation of the RTF as it passes in each direction, to make it compatible with the target.


    Leigh Webber

    Tuesday, October 29, 2013 7:35 PM
  • Thanks for the ideas. All of this is done with code, but yields terribly unreliable results. I think the final answer is to create Word templates for each of the documents and leave all the text manipulation in Word. Access will simply manage the templates, create new documents and keep track of them. That will eliminate all the issues of what Access RTF may or may not support. Thanks for the help.
    Wednesday, October 30, 2013 1:55 PM
  • Thanks.  Very helpful.

    Did find that when I copy this in Word and run your Test sub

    日本のフォルダ

    I get an Overflow error.  This line in SetClipboardText

    Dim wLen As Integer

    needs to be changed to 

    Dim wLen As Long

    The same Dim in GetClipboardText can be removed (not used).

    Friday, January 10, 2014 11:52 PM
  • Hello Leigh!

    Thank you very much for supplying this precious class! Since it's a little hard both to find and read in this forum (without code markup etc.) I would suggest you turn it into a codeplex (or any other source hoster :)) project, so people can find it and also reward your work.

    If for any reason you don't want to do this yourself I would kindly ask for your permision to refactor your code to work on 64-bit Office versions also and upload it as a codeplex project. I will of course link to this original post in my description.

    Thank you again for your great work!
    Merlin

    • Edited by AmiMerlin Sunday, September 07, 2014 6:50 PM
    Sunday, September 07, 2014 6:49 PM