after upgrade to windows 10 vba dataobject.putinclipboard stops working

after upgrading windows 7 pro to windows 10 pro the following code does not work in excel 2010 word 2010 or outlook 2010 .  I have found other forums that confirm this as a problem and give workarounds using other APIs, but I really hate searching dozens of my programs to replace simple code with complicated APIs.

I have tried repairing office 2010 pro, but that did not solve the problem.

Does anybody have a fix? If not does anybody have a computer where this works with windows 10 and excel 2010 ?

Sub test()
Dim Dto As New DataObject
Dto.SetText "test"
Dto.PutInClipboard

Dto.GetFromClipboard
mystring = Dto.GetText
MsgBox " this shows that the clipboard contains <" & mystring & ">. BUT if you paste the clipboard into notepad, you get ??"


End Sub
LVL 5
rberkeConsultantAsked:
Who is Participating?
 
DrTribosConnect With a Mentor Commented:
Just for fun... try a different user profile on your pc....
0
 
DrTribosCommented:
Works with Word 2010  in Win 10...  Message box displayed "test" pasted to immediate window: "test"
0
 
DrTribosCommented:
Worth mentioning for any VBA noobs that in order for this to even compile you need a reference to  the "Microsoft Forms 2.0 Object Library" which you can find via: Tools > References, then browse and add the reference (or just add a userform to your project and it will appear automagically).
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
aikimarkCommented:
I'm a fan of late binding, which doesn't rely on type library references in the VBA project.
0
 
rberkeConsultantAuthor Commented:
Since DrTribos told me it worked for him, I decided to update a coworker's Office 2010 Home and Business computer from windows 7 pro to windows 10.  The code works on that computer, but not on mine.

I have already tried the following:
* Rebooting
* repairing my office 2010 pro
* copying the coworker's c:\windows\sysWOW64\fm20.dll on top of mine followed by  Regsvr32

What to do next? I am toying with running an sfc /scannow, but that seems a little drastic.  And, even more drastic would be to revert to Windows 7.

Also, the following code is interesting. Run it, then click OK and the .gettext shows clipboard with '??'.  But, if you remove linexxx, the .gettext shows "test2"
Sub test()

Dim Dto As New DataObject
        Dto.SetText "test2"
        Dto.PutInClipboard
linexxx:  MsgBox "click OK"
        Dto.GetFromClipboard
        MsgBox " clipboard now has " & Dto.GetText
End Sub

Open in new window

0
 
rberkeConsultantAuthor Commented:
Should I laugh ?? or should I vomit ??

I signed on as Administrator and the program worked.
I then signed on as mydomain\rberke  and the programs now works !!!!  Outlook, Word, Excel, MS Access are all fine !!!

How much other crap do you suppose I have in store ?
Normally, I would just rebuild my profile and get it over with - but the company wants to migrate everybody to windows 10.  The idea of rebuilding everybody's profile is not pleasant, so I think I will stick with my current profile and see what other land mines I encounter.

Thanks for the suggestion, and here come the points.
0
 
DrTribosCommented:
vol - yep that's a new one... vomit out loud.  And, enjoy the extra advertising that the anniversary edn of W10 promises to deliver...  welcome
0
 
rberkeConsultantAuthor Commented:
bad news.  The problem has resurfaced in mydomain\rberke profile (the localcpu\administrator profile still works properly)

I googled for "rebuild windows 10 profile" and found lots of horror stories.  It appears the only reliable way to upgrade to windows 10 is to reformat the disk and reinstall windows 10 form scratch.  But some folks report good results with creating a new profile, so that is what I will do.

This will have to wait until the weekend so I'll post back after the work is done.
0
 
DrTribosCommented:
More v less l 😣
0
 
Excel KidCommented:
Hello,

Sorry to pile on on an old post. But I seem to have the exact same problem.
I have used this little code in a number of places and replacing it with those complicated API calls seems like a horrible solution.

Did you actually fix this on your win10 machine? Could you please share the solution if you did?
0
 
rberkeConsultantAuthor Commented:
"Did you actually fix this on your win10 machine?"

Possibly.  try this
Log of yourdomain\yourname
Switch user and log in as local administrator   cpu123\administrator
open word and excel
logoff of cpu123\administrator
log on as yourdomain\yourname.

I did the above about two weeks ago, and the dataobject problem has gone away.  But it was so random before that I can't be sure it is completely resolved.  By the way, I am still using the same user profile that was corrupted.

Please post back how well that worked.
0
 
rberkeConsultantAuthor Commented:
My fix worked for about 4 weeks, but then stopped working.

I still hope that creating a completely new profile will solve the problem, but I won't be doing that anytime soon.

Bob
0
 
DrTribosCommented:
You know what...

I'd really like to start collecting information for all these issues.... there must be something buried in the registry somewhere.  I've started collecting registry keys for PCs that crash MS Word for no reason.... (but when I say "just started", I mean it.... so far I have 1 key)

Word can be so temperamental... how about create a new 'spare' profile and test that the issue is not present.  Continue using the old profile...  see if you can fix the old profile.... then when it breaks again... see if it is also broken on the spare profile.... (i.e. that was created some time ago).
0
 
rberkeConsultantAuthor Commented:
I finally have something that is repeatable. If any Explorer window is displaying any folder on  C:  or D: or E  then databobjects do not work.

if windows explorer is closed dataobjects work perfectly.

Dataobject fail if I have opened these 3 explorer windows:  1) This Pc    2) Quick access     3) desktop  
dataobjects work if I only have these open  1) This Pc    2) Quick access    

It appears that when any explorer window is related to a physical disk drive, dataobjects fail.

Bu dataobjects workt if all explorer windows are displaying a "simulated folder" which is entirely in the registry.

Right now, this is totally repeatable.  I am going to reboot and see if things change.

Sub t1246()
Dim test, newClip As String

Set test = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") ' late binding to dataobject
        test.SetText "test"
        test.PutInClipboard
        Application.Wait (Now + TimeValue("00:00:02"))
       
        test.GetFromClipboard ' sometimes, when corruption occurs, debugger stops even when there is no error
        newClip = test.GetText
MsgBox "dataobject clipboard returned <" & newClip & ">"
End Sub
1
 
rberkeConsultantAuthor Commented:
Excel Kid:
Last month you said you were having the exact same dataobject problem that I described.

If you still have the problem, please check something for me:  Close ALL  Explorer display windows and see if the problem goes away.

Then, open c:\  to display the root folders and see if the vba problem returns.
1
 
rberkeConsultantAuthor Commented:
I believe the upgrade to windows 10 corrupted the MyDomain\rberke  profile.  
Under that profile, the dataobject does not work when Explorer is displaying a folder like c:\...   d:\... or  \\share\..,

Under other profiles like MyDomain\rberke2 everything seems to work fine.  Of course, if I used  mydomain\rberke2 for several weeks the corruption might return - but I don't think it will.
0
 
rberkeConsultantAuthor Commented:
A lot has happened since I posted this problem long ago.

I have scrapped my old lenovo computer and now have a much more powerful computer that came with windows 10 pre installed.

Plus, the computer is now attached to a Windows Server 2016 domain controller instead of the old SBS 2003.

The data objects problem still occurs randomly.  So, I can conclude either:

1.  Profile corruption is not the cause of the problem.

OR,

2. Profile corruption might be the cause of the problem, but the corruption re-occurs fairly quickly after rebuilding the profile.

In either case, I no longer recommend rebuilding a profile: the only reliable solution I have found is to stop using DataObjects.
I have converted all my programs to use APIs. Here are the ones I use, but there are lots of alternatives on the web.

Option Explicit

Private Declare Function OpenClipboard Lib "user32.dll" (ByVal hwnd As Long) As Long
Private Declare Function EmptyClipboard Lib "user32.dll" () As Long
Private Declare Function CloseClipboard Lib "user32.dll" () As Long
Private Declare Function IsClipboardFormatAvailable Lib "user32.dll" (ByVal wFormat As Long) As Long
Private Declare Function GetClipboardData Lib "user32.dll" (ByVal wFormat As Long) As Long
Private Declare Function SetClipboardData Lib "user32.dll" (ByVal wFormat As Long, ByVal hMem As Long) As Long
Private Declare Function GlobalAlloc Lib "kernel32.dll" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Private Declare Function GlobalLock Lib "kernel32.dll" (ByVal hMem As Long) As Long
Private Declare Function GlobalUnlock Lib "kernel32.dll" (ByVal hMem As Long) As Long
Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function lstrcpy Lib "kernel32.dll" Alias "lstrcpyW" (ByVal lpString1 As Long, ByVal lpString2 As Long) As Long


Public Sub SetClipboard(ByVal sUniText As String)
                  
    Dim i As Long
    Dim iStrPtr As Long
    Dim iLen As Long
    Dim iLock As Long
    Const GMEM_MOVEABLE As Long = &H2
    Const GMEM_ZEROINIT As Long = &H40
    Const CF_UNICODETEXT As Long = &HD

    OpenClipboard 0&
    EmptyClipboard
    iLen = LenB(sUniText) + 2&
    iStrPtr = GlobalAlloc(GMEM_MOVEABLE Or GMEM_ZEROINIT, iLen)
    iLock = GlobalLock(iStrPtr)
    lstrcpy iLock, StrPtr(sUniText)
    GlobalUnlock iStrPtr
    SetClipboardData CF_UNICODETEXT, iStrPtr
    CloseClipboard
End Sub
Public Function GetClipboard() As String

    Dim iStrPtr As Long
    Dim iLen As Long
    Dim iLock As Long
    Dim sUniText As String
    Const CF_UNICODETEXT As Long = 13&
    OpenClipboard 0&
    If IsClipboardFormatAvailable(CF_UNICODETEXT) Then
        iStrPtr = GetClipboardData(CF_UNICODETEXT)
        If iStrPtr Then
            iLock = GlobalLock(iStrPtr)
            iLen = GlobalSize(iStrPtr)
            sUniText = String$(iLen \ 2& - 1&, vbNullChar)
            lstrcpy StrPtr(sUniText), iLock
            GlobalUnlock iStrPtr
        End If
        GetClipboard = sUniText
    End If
    CloseClipboard
End Function

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.