Search code examples
excelvbastringclipboard

How to copy from and to clipboard?


I have two codes (with the help of other users):
#1 makes a specific text string using the current range and puts it into the clipboard.
#2 copies from the clipboard, removes some unwanted characters and puts the modified string back to the clipboard.

These codes do not work in some of the company machines.
All have the Microsoft Forms 2.0 Object Library checked.
enter image description here

I've read about using the HTML method but I couldn't figure it out.

Here are the codes:

#1

Sub x_copiar_nome_arquivo()

    Dim DataObj As New MSForms.DataObject
    Dim arquivo As String
    
    On Error GoTo ErrorHandler
    With Selection.Rows(1).EntireRow
        arquivo = .Range("G1").Value & " - " & Format(.Range("E1").Value, "#000000000")
    End With
    With DataObj
        .SetText arquivo
        .PutInClipboard
    End With
    
ErrorHandler:
    Exit Sub
    
End Sub

#2

Sub y_chave_danfe()
    
    Dim DataObj As New MSForms.DataObject
    Dim chave As String
    Dim danfe As String
    
    On Error GoTo ErrorHandler
    DataObj.GetFromClipboard
    chave = DataObj.GetText
    danfe = Replace(Replace(Replace(Replace(Replace(chave, " ", ""), "/", ""), "-", ""), ".", ""), "_", "")
    With DataObj
        .SetText danfe
        .PutInClipboard
    End With
    
ErrorHandler:
    Exit Sub
    
End Sub

Solution

  • The final version of my question ended up with a code like so:

    It gets whatever it's on the clipboard, remove everything keeping only digits (0-9) and puts the new "number" back to the clipboard.

    Public Sub y_chave_danfe()
    
    Dim atransf As New MSForms.DataObject
    Dim chave As Variant
    Dim danfe As String
    Dim z As Long
    
    danfe = vbNullString
    atransf.GetFromClipboard
    chave = atransf.GetText
    
    For z = 1 To Len(chave)
        If IsNumeric(Mid(chave, z, 1)) Then
            danfe = danfe & Mid(chave, z, 1)
        End If
    Next z
    
    CreateObject("htmlfile").parentWindow.clipboardData.setData "text", danfe
    
    End Sub