CODEWITHSUNDEEP
excelvbaclipboard
Cody Mayers
Cody Mayers

Reputation: 385

VBA .SetText and .PutInClipboard putting two symbols in clipboard instead of desired data

Using Excel 2016 and a reference to the Microsoft Forms 2.0 Object Library, I'm trying to copy the ActiveCell's contents to my clipboard. Instead, the resulting contents of my clipboard are the following 2 symbols (if they'll actually show up in this text field.

��

��

(In case those symbols aren't rendering, in the StackOverflow website's text editor they look like white rectanges. Depending on the text editor I'm pasting it in, they've also resembled a question mark, a black diamond containing a white question mark, and just a blank space as if the space bar was pressed.)

I'm not trying to copy symbols of any kind, it's plain English. I've used code similar to this in other macros and it's always worked until today. The code itself is below. I hope you can help!

Dim clipboard As New MSForms.DataObject

clipboard.SetText ActiveCell.Value
clipboard.PutInClipboard

Debug.Print clipboard.GetText(1)    
Set clipboard = Nothing

The Debug.Print command prints out the desired text, but after the macro finishes, the desired text is not there and instead there are the 2 symbols again.

Upvotes: 5

Views: 7689

Answers (5)

burnie
burnie

Reputation: 11

It's 2025 now and this problem still persists. My quite simple solution is using PowerShell Set-Clipboard cmdlet:

Dim clipboard As New MSForms.DataObject

clipboard.SetText ActiveCell.Value
Shell ("powershell Set-Clipboard -Value '" & clipboard & "'")

Instead of using the VBA PutInClipboard method, I'm using the PowerShell Set-Clipboard cmdlet, executed by a Shell call, to copy the data to the clipboard. This works without any issues even if the Windows file explorer is opened in the background.

It's also much easier than using Windows API calls for getting access to clipboard functions in VBA.

Upvotes: 0

Brad Earle
Brad Earle

Reputation: 1

I tried a different approach...

I found that if I pasted the clipboard to Notepad++, then copied that same text back to clipboard, then continued with the macro, vba then continued fine.

So, taking the above action into account, I just did the same sort of thing in vba code.

created a second data object created a text object copied the text from the first object's clipboard, took that text and placed it into the second object, then put the second object into the clipboard.

something like this (cb is the first object, cb2 is the new empty object and cbtext is the string to copy the text from the first object)

    cb.PutInClipboard   ' this is the only code that was here, but inconsistently failed.
' added this code
' round about way of ensuring copied text pastes as text
    Dim cbtext As String
    cbtext = cb.GetText
    cb2.SetText (cbtext)
    cb2.PutInClipboard

Upvotes: 0

Bill Simpson
Bill Simpson

Reputation: 1

The effect seems version dependent, whether the explorer window is opened before or after Excel. It also depends on what is in the explorer window - if it is a 'system location' such as "This PC", then putinclipboard still works normally.

I had tried various options for ages, but eventually found one workaround - acting as if copying manually...

In this case, the text to be copied to the clipboard is in the active cell, but the approach can be adapted for other circumstances:

Sub AACopyText() 'has to be called from Excel workbook (ie not from the VB window!)

SendKeys "{F2}^a^c{ESC}"

End Sub

F2 activates the contents of the cell, ^a selects the entire contents, ^c copies the contents to the clipboard; {ESC} is then required or the cell contents remain active.

if the required text is not the activecell content, for example is in a variable eg MyTextToCopy then the activecell can be temporarily over-written:

    AppActivate ActiveWorkbook.Name
    SendKeys "{F2}^a" & MyTextToCopy & "^a^c{ESC}"

These lines activate the activecell in the active workbook (so can be used in a procedure called from a form, etc), selects the contents, overwrites the contents, copies the new contents, then returns the original contents.

Alternatively, use can be made of Notepad; the following routine will work in programs other than Excel:

Function clip(ClipText As String)
  On Error Resume Next
  Dim WasntOpen As Boolean
  AppActivate ("Notepad")
  If Err Then
    WasntOpen = True
    Err.Clear
    x = Shell("Notepad.exe", vbNormalFocus)
    AppActivate ("Notepad")
  End If
  SendKeys "^a" & ClipText & "^a^c^z"
  If WasntOpen Then SendKeys "%Fx"
End Function

where 'ClipText' is the text you want to save eg In your routine, the line Clip("1234") will put "1234" on the clipboard

Upvotes: -1

Rolando Torrico
Rolando Torrico

Reputation: 11

One way that worked for me is;

-Close Excel and File Explorer.

-Reopen Excel test the functionality of PutInClipboard /paste

-Open Window 10 File Explorer

-Test again.

*I work for me why? I don't know but it seems that excel has to be open prior to File Explorer.

Upvotes: 1

dw cook
dw cook

Reputation: 81

In Windows 10, if file explorer is open the putinclipboard does not work. Go figure.

https://www.mrexcel.com/board/threads/copy-cell-address-to-clipboard-issue-putinclipboard-not-working.983442/

Upvotes: 7

Related Questions