• Posted on: 11 September 2013
  • By: greeny

Option Explicit

'##########################################################################################
'REGION: API
Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
'END REGION
'##########################################################################################

Public Sub Main()
           
    Dim clipboard As MSForms.DataObject
    Dim strContents As String, strGUID As String
    Dim fso As New FileSystemObject
    Dim fileTxt As Scripting.TextStream
   
    Set clipboard = New MSForms.DataObject
   
    clipboard.GetFromClipboard
    strContents = clipboard.GetText
   
    If clipboard.GetFormat(1) = True Then
        strGUID = Format(Now, "YYYYMMDDHHNNSS") & "PlainText.txt"
        Open Environ("TEMP") & "\" & strGUID For Output As #1
        Print #1, strContents
        Close #1
       
        Application.CutCopyMode = False
        Call ClearClipboard
               
        Set fileTxt = fso.OpenTextFile(Environ("TEMP") & "\" & strGUID, ForReading)
        strContents = fileTxt.ReadAll
        fileTxt.Close
       
        Set clipboard = New MSForms.DataObject
                      
        Kill Environ("TEMP") & "\" & strGUID
       
        clipboard.SetText strContents
        clipboard.PutInClipboard
    End If
   
    ActiveSheet.Paste
   
End Sub

'##########################################################################################
'REGION: HELPERS
Private Function ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
End Function
'END REGION
'##########################################################################################

 

VBA: workaround to insert text from clipboard as plain text