Copy Cell Address to clipboard issue - PutInClipboard not working

[amarsbar]

Hi,

I previously used the following macro to copy a cell address to the clipboard, and subsequently paste elsewhere:

'Public Sub CopySelectionAddressToClipboard()


Dim rngT As Range
Dim strAddress As String
Dim dobClip As DataObject


If Application.Selection.count = 0 Then Exit Sub

Set dobClip = New DataObject
Set rngT = ActiveSheet.Range(Application.Selection.Address)

strAddress = "'" & rngT.Parent.Name & "'!" & rngT.Address

strAddress = Replace(strAddress, "$", "")

dobClip.SetText strAddress
dobClip.PutInClipboard

Set dobClip = Nothing


End Sub'

I have now got a new PC and when I paste what I have copied I get 2 question marks instead of the cell address. I am not sure on how to fix this and any help on this issue would be much appreciated.

I am using windows 10 and excel 2016 (32 bit).

Thanks

 

[MickG]

I also had this trouble.
I found that if I had any folder open in "File Explorer" then I am unable to use the "DataObject" to store in clipboard.
Once I've closed "File Explorer" the problem goes away.
Perhaps your solution could be the same ????
I have XL2010 and windows 10

 

[amarsbar]

Thank you so much Mick, that has worked for me too. Very odd quirk! I will think of a way to correct this and update this post if I find

 

[MickG]

You're welcome
I least its nice to know I'm not the only one!!!

 

[AlphaFrog]

Test if this is a workaround...

[color=darkblue]Sub[/color] CopySelectionAddressToClipboard2()
    [color=green]'late binding to copy text to clipboard.[/color]
    [color=darkblue]If[/color] Application.Selection.Count = 0 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]With[/color] CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText "'" & ActiveSheet.Name & "'!" & Selection.Address(0, 0)
        .PutInClipboard
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[RoryA]

The best way around this that I am aware of is to use Windows API calls instead of the DataObject.

 

[MickG]

AlphaFrog:-
Unfortunately that also fails !!!!
I would obviously prefer not to have this problem ,but its not too difficult to close a folder!!

 

[arkusM]

Whiskey.
Tango.
Foxtrot.

I spent 20min trying to figure out why the .PutInClipboad was not working today and this is the workaround??? (Shakes Head)
Well now I know.
Microsoft your[sic] the best! 11

I also had this trouble.
I found that if I had any folder open in "File Explorer" then I am unable to use the "DataObject" to store in clipboard.
Once I've closed "File Explorer" the problem goes away.
Perhaps your solution could be the same ????
I have XL2010 and windows 10

 

[Mr Boris]

The best way around this that I am aware of is to use Windows API calls instead of the DataObject.

Yes agreed, have implemented this approach too, however closing Windows File Explorer does seem to have the suggested approach of allowing the M$ Forms 2.0 approach to actually work! WTF

For those looking for an API solution that works with Excel 2016 64bit, under win10 x64 this post on SO worked immediately for me when i changed my variable to a string.

https://stackoverflow.com/questions/18668928/excel-2013-64-bit-vba-clipboard-api-doesnt-work

I use the routine to "paste" RangetoHTML code into a populated MailChimp mail-out - my issue NOW is that Outlook 2016 does not display HTML emails correctly

Sigh .. Good on ya M$ .. NOT