A clipboard object for VBA, including Microsoft Word

Article
2010-07-26

Question

_{Monday, July 26, 2010 5:12 PM | 6 votes}

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

All replies (49)

_{Monday, July 26, 2010 5:18 PM | 1 vote}

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:25 PM}

Ignore that. I edited my original post and fixed it.

_{Monday, July 26, 2010 6:37 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

_{Tuesday, January 3, 2012 5:13 AM}

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

_{Tuesday, January 3, 2012 8:19 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

_{Wednesday, January 4, 2012 7:11 PM}

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 4, 2012 8:08 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

_{Thursday, February 9, 2012 2:47 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 9, 2012 3:36 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 9, 2012 3:41 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 9, 2012 5:01 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 9, 2012 7:55 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\](file://\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

_{Thursday, February 9, 2012 8:18 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 9, 2012 8:47 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

_{Friday, February 10, 2012 5: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 6:40 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

_{Tuesday, February 14, 2012 2:41 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 5:01 PM | 1 vote}

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

_{Wednesday, February 22, 2012 12:54 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 8:33 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

_{Monday, November 5, 2012 1:32 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

_{Tuesday, November 6, 2012 2:47 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

_{Sunday, March 3, 2013 1:48 PM}

Hello,

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

Dim clipboardFormat As clipboardFormatA workaround very welcomePawel

_{Friday, March 8, 2013 8:19 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

_{Monday, March 11, 2013 11:58 AM}

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

_{Wednesday, April 24, 2013 12:29 PM}

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 2:16 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 3:17 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 4:03 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:25 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 7:06 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:38 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 9:20 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:29 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 10:23 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:25 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

_{Thursday, April 25, 2013 6:54 AM}

Glad it helped, but bear in mind that inlineshape objects could be very large!

Peter Jamieson

_{Wednesday, August 21, 2013 8:23 PM}

I wish this code ran under 64-bit Office. I cannot get 'Function GlobalLock Lib "kernel32"' to work when declared as PtrSafe...

_{Tuesday, August 27, 2013 8:19 PM}

Absolutely awesome, thank you so much for this! :-)

_{Tuesday, October 29, 2013 6:14 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 7:35 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

_{Wednesday, October 30, 2013 1:55 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.

_{Friday, January 10, 2014 11:52 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).

_{Sunday, September 7, 2014 6:49 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

_{Monday, March 7, 2016 2:26 PM}

Hi Leigh, Thanks Post.

I have a question that, if i copy some content from "web browser" or " office word document/outlook mail item "

or copy files like " PDF file", or ".txt" , or ". rar "etc.

Can i restore the the clipboard content back, after using the "VBA selection.copy " ?

Is there any new technic available for us?

Thanks and look forward! Appreciated.

Tom

_{Monday, February 4, 2019 9:05 PM}

Brilliant! This was much better than the partial solutions that you referred to that were floating around the Web.

For the record, I tried using your class modules in an Excel VBA project, and although you say this is designed for use in Word VBA, it worked for me nonetheless. I also tried the Data Object solution, which also worked. However, your solution is superior as, unlike the Data Object solution, it doesn't rely on the end user having the Microsoft Forms 2.0 Object Library on their machine and avoids the extra steps of either ensuring the control is available and/or shipping and installing the control.

I do have a question. There are some clipboard formats that are specific to Excel that are not in the list of formats in your class module. There is a list here:

https://docs.microsoft.com/en-us/office/vba/api/excel.xlclipboardformat

The few tests I've tried so far seem to show that your class modules support copying and pasting both values and formulae as well as other things like formatting, as-is. Therefore, I have no need to make any modifications as of yet. However, if necessary, I think I could modify the class modules to support some of these Excel clipboard formats. What puzzles me, though, is that some of the Excel clipboard formats seem to translate to the same format number as some of the ones already in your class module, even though the names of the formats are different and therefore seems to imply that the formats are indeed different. I was wondering if you might have any insight into this?

Thanks for sharing this with the rest of the VBA community!

Joe Brooks

P.S. I'm not quite as old (I'm from the era immediately after the one where punch cards were in common use - early 80's), but I have used printer terminals and played the Star Trek game to which you're referring.

_{Monday, February 4, 2019 10:19 PM}

It has been a few years since I looked at this thread, and in the meantime I have moved away from intensive Word (or Excel) VBA work. I don't think I can help much with your question. One thought, though, is that I suspect that the format numbers are the important thing. The format names might well be specific to the application you are using -- sort of like how strings found in resource files can be replaced by other-language strings simply by pointing to a different resource file. You might try fiddling around with the formats whose numbers but not names match between Excel and Word and see what happens.

Leigh Webber

_{Thursday, September 26, 2019 8:32 AM | 1 vote}

Dear LeighWebber

MANY THANKS!!

I just needed to change api function declarations for Excel 64 bits:

#If VBA7 Then
    Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
    Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
    Private Declare PtrSafe Function EnumClipboardFormats Lib "user32" (ByVal wFormat As Long) As Long
    Private Declare PtrSafe Function GetClipboardFormatName Lib "user32" Alias "GetClipboardFormatNameA" (ByVal wFormat As Long, ByVal lpString As String, ByVal nMaxCount As Long) As Long
    Private Declare PtrSafe Function RegisterClipboardFormat Lib "user32" Alias "RegisterClipboardFormatA" (ByVal lpString As String) As Long
    'Note that we do not use the GetClipboardDataA declaration
    'Private Declare Function GetClipboardData Lib "user32" Alias "GetClipboardDataA" (ByVal wFormat As Long) As LongPtr
    Private Declare PtrSafe Function GetClipBoardData Lib "user32" Alias "GetClipboardData" (ByVal wFormat As Long) As LongPtr
    Private Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As Long, ByVal hMem As LongPtr) As LongPtr
    Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
    Private Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As Long
    'NOTE: the lstrCpy declaration you get from the VB6 API Viewer is WRONG. It's version is this:
    'Private Declare PtrSafe Function lstrcpy Lib "kernel32" Alias "lstrcpyA" (ByVal lpString1 As String, ByVal lpString2 As String) As LongPtr
    'The correct version for (at least for Windows 7 / 64-bit is this:
    Private Declare PtrSafe Function lstrCpy Lib "kernel32" Alias "lstrcpyA" (ByVal lpString1 As Any, ByVal lpString2 As Any) As LongPtr
    Private Declare PtrSafe Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Long) As Long
    Private Declare PtrSafe Function GlobalSize Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
    Private Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As LongPtr) As LongPtr
    Private Declare PtrSafe Function GlobalFree Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
#Else
    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
    'Private 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
#End If

And also some variable declarations, like for example:

#If VBA7 Then
    Dim hMemory As LongPtr, lpMemory As LongPtr
#Else
    Dim hMemory As Long, lpMemory As Long
#End If

_{Thursday, September 26, 2019 4:51 PM}

Sorry,

Just to report that i'm getting an error every second time SetClipboardText is called,

if i paste the clipboard data before the second call, it seems to works ok,

but calling 2 times the function (without pasting before) seems to fail

I was unable to fix the error,

then found this simpler code that is working ok in my tests on Excel 2016 64-Bits. See:

Text To Clipboard in VBA Windows 10 Issue

answer by LockeGarmin, Feb 18, 2016

www.stackoverflow.com/questions/35416662/text-to-clipboard-in-vba-windows-10-issue

Share via

A clipboard object for VBA, including Microsoft Word

Question

All replies (49)

Additional resources