Get text from clipboard using GetText – avoid error on empty clipboard

by

Handle the errors with On Error GoTo as shown here:

Sub GetClipBoardText()
   Dim DataObj As MSForms.DataObject
   Set DataObj = New MsForms.DataObject '<~~ Amended as per jp's suggestion

   On Error GoTo Whoa

   '~~> Get data from the clipboard.
   DataObj.GetFromClipboard

   '~~> Get clipboard contents
   myString = DataObj.GetText(1)
   MsgBox myString

   Exit Sub
Whoa:
   If Err <> 0 Then MsgBox "Data on clipboard is not text or is empty"
End Sub

You will notice that it will handle an empty clipboard as well.

NB: to make the code work, you must have a reference to “Microsoft Forms 2.0 Object Library” (this file can be found at C:\windows\system32\FM20.dll on 32-bit machines, or at C:\Windows\sysWOW64\FM20.dll on 64-bit machines), otherwise you’d get the error “User-Defined type not defined”.

You can empty the clipboard before testing the above code by using the code below. Please paste it in a module.

Private Declare Function OpenClipboard Lib "User32.dll" _
(ByVal hWndNewOwner As Long) As Long
  
Private Declare Function EmptyClipboard Lib "User32.dll" () As Long
 
Private Declare Function CloseClipboard Lib "User32.dll" () As Long
 
Public Sub ClearClipboard()
    Dim Ret
  
    Ret = OpenClipboard(0&)
    If Ret <> 0 Then Ret = EmptyClipboard
    CloseClipboard
End Sub

EDIT: you may also determine if the clipboard is empty by using this code:

Private Declare Function CountClipboardFormats Lib "user32" () As Long

Sub Sample()
    If (CountClipboardFormats() = 0) = True Then
        MsgBox "Clipboard is empty"
    Else
        MsgBox "Clipboard is not empty"
    End If
End Sub

More Related Contents:

  1. Copy Paste Values only( xlPasteValues )
  2. Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table
  3. Excel VBA code to copy a specific string to clipboard
  4. Excel macro – paste only non empty cells from one sheet to another
  5. VBA copy rows that meet criteria to another sheet
  6. Sequence of numbers based on start and end value of 2 cells [closed]
  7. Function to convert column number to letter?
  8. Read/Parse text file line by line in VBA
  9. Better way to find last used row
  10. 2 Dimensional array from range [closed]
  11. filter out multiple criteria using excel vba
  12. Excel VBA autofilter all but three
  13. Using Google Chrome in Selenium VBA (Installation Steps)
  14. Dir() function not working in Mac Excel 2011 VBA
  15. How to use workbook.saveas with automatic Overwrite
  16. Range limit conundrum
  17. How do you run a .exe with parameters using vba’s shell()?
  18. How to use FindWindow to find a visible or invisible window with a partial name in VBA
  19. How do you get a Range to return its Name?
  20. Passing variable from Form to Module in VBA
  21. Excel VBA: Convert a date string to a Unix timestamp
  22. excel to pdf ignores print area and calls printer
  23. Leave out quotes when copying from cell
  24. In Excel, can I use a hyperlink to run vba macro?
  25. excel vba freeze pane without select
  26. Converting a string representation of a constant into a constant?
  27. Macro to export MS Word tables to Excel sheets
  28. VBA WinHTTP to download file from password proteced https website
  29. How to highlight selected text within excel
  30. How to fix a VBA “type mismatch” error after switching to 64-bit Excel

Leave a Comment