Tests Copying, Pasting, API Cliipboard issues. and Rough notes on Advanced API stuff

Some rough Clsid Clipboard notes on this page ( https://www.excelfox.com/forum/showt...-issues/page55
https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-pasting-Cliipboard-issues/page55 )

---

---

---

In the first 3 posts is a few links, probably to be added to from time to time, and a few very very rough notes, mainly for me and unlikely useful for anyone else. More useful stuff for others is further down this Page #54 , from post #534


2007 ht t ps://microsoft.public.word.vba.general.narkive.com/5LFhze6E/clear-office-clipboard-from-vba#post10 Tony Jollans If you are using Office 2000 you do have some indirect access to the Office Clipboard from VBA - but you do not have it in any later versions. In Office 2000 the Office Clipboard is presented as a CommandBar and so you can programmatically manipulate the Controls, for example ... CommandBars("Clipboard").Controls("Clear Clipboard").Execute
In Office XP and later, the Office Clipboard is presented as a Task Pane and the 'back door' has been shut. AFAIK, VBA access to it post-2000 is impossible - I don't think you can even do it with SendKeys (but don't quote me on that).
2007 ht t ps://microsoft.public.word.vba.general.narkive.com/5LFhze6E/clear-office-clipboard-from-vba#post13 Tony Jollans ….. Loosely, what happens is this: when you do a Copy operation the Windows Clipboard is cleared and whatever it is you are copying is placed in the Windows Clipboard. It may be added in several different formats (it's not Really relavant now, but some of them may go onto the clipboard itself, some of them may be pointers for the originating application to act on later request). When you do a Paste, the Windows Clipboard is asked to provide whatever is on it in the format you want and, providing that format is available, it will provide it, but still maintain whatever it holds for you to do further Pastes if you wish. In normal circumstances you should not need to do an explicit emptying of the clipboard yourself.
Separate to all this there is, again (very) loosely, a Windows event fired when something is added to the Windows Clipboard which allows Office to know that it has happened and take a copy for the Office Clipboard. Entirely separate from the Windows Clipboard, the Office Clipboard maintains its own copies of up to 12 (Office 2000) or 24 (later versions) items. Office provides some UI facilities for manipulating the copies it holds but they are not directly available from code and no Paste operation, other than explicitly from the Office Clipboard, will use them. If you do an explicit Paste from the Office Clipboard (via the UI) what actually happens is that a (Windows) Copy operation is triggered to copy from the Office Clipboard to the Windows Clipboard and a (Windows) Paste operation is then triggered to paste from the Windows Clipboard to the specified destination.
I confess myself at a total loss to explain how something that has been removed from the Windows Clipboard can later be Pasted; it can only happen if it has be re-placed (i.e. re-copied) on to the Windows Clipboard again - whether explicitly or implicitly as part of some other operation. So the immediate questions must be: can you verify that your copies have worked? can you verify that your clearing of the Windows clipboard has worked? and what do you do between copying and pasting? -- Enjoy,
ht t ps://microsoft.public.word.vba.general.narkive.com/5LFhze6E/clear-office-clipboard-from-vba#post9 hybrid "thing" offered by Office

May 2010 h tt ps://web.archive.org/web/20100506094659/ht tps://benf.org/excel/officeclip/index.html
ht t ps://web.archive.org/web/20100506094659/http://support.microsoft.com/default.aspx/kb/221190 no joy
May 2008 ht t ps://web.archive.org/web/20080508194547/http://msdn.microsoft.com/en-us/library/ms649052(VS.85).aspx

Jan 2015 RickXL madness https://www.mrexcel.com/board/thread.../#post-4040646 2 you could paste to either Excel, Notepad or Paint. The clipboard has a native copy for Excel, a text version for Notepad, a bitmap for Paint, an HTML version, an XML version etc etc. According to my recent tests, it seems as if Excel saves about 29 sets of data when you make a copy. When you then try and paste it looks through the list of formats on the clipboard and finds the most suitable one

Aug 2015 htt ps://wellsr.com/vba/2015/tutorials/vba-copy-to-clipboard-paste-clear/ Rory knicked? 2018 Update: With the rollout of Windows 8 and Windows 10, this solution no longer works reliably,



2018 htt ps://chandoo.org/forum/threads/clipboard-copy-vba-code-not-working-in-windows-10.37126/#post-223256 Masturbator API


12/29/2018 ht t ps://web.archive.org/web/20191220024152/ht tps://docs.microsoft.com/en-us/office/vba/language/concepts/forms/what-is-the-difference-between-the-dataobject-and-the-clipboard The DataObject and the Clipboard both provide a means to move data from one place to another. DataObject is a standard OLE object, while the Clipboard aint

Feb 2019 https://eileenslounge.com/viewtopic....246740#p246740 3 Reset Clear Clipboard
Feb 2019 https://eileenslounge.com/viewtopic....246740#p246740 3 My good links Reset Clear Clipboard
https://eileenslounge.com/viewtopic....246884#p246884 2
https://eileenslounge.com/viewtopic....246838#p246838 2
https://eileenslounge.com/viewtopic....246770#p246770 1 with La légende du bouton :)
ht tps://docs.microsoft.com/en-us/office/vba/language/concepts/forms/what-is-the-difference-between-the-dataobject-and-the-clipboard changes to
ht tps://learn.microsoft.com/en-us/office/vba/language/concepts/forms/what-is-the-difference-between-the-dataobject-and-the-clipboard 09/13/2021

Mar 2019 https://stackoverflow.com/questions/...60767#54960767 2

Mar 2019 https://eileenslounge.com/viewtopic....247809#p247809 3 (Clipboard API alternative) array overflow date value Yasser rory
Mar 2019 My last post is best, https://eileenslounge.com/viewtopic....247809#p247809 , giving further links
Oct 2019 ht tps://social.msdn.microsoft.com/Forums/en-US/48e8c30c-24ee-458e-a873-a4e6e13f5926/dataobject-settext-and-putinclipboard-sequence-puts-invalid-data-hex-63-characters-in-clipboard?forum=isvvba not working so did archive org below which works
htt ps://web.archive.org/web/20200806111619/h ttps://social.msdn.microsoft.com/Forums/en-US/48e8c30c-24ee-458e-a873-a4e6e13f5926/dataobject-settext-and-putinclipboard-sequence-puts-invalid-data-hex-63-characters-in-clipboard?forum=isvvba
That seems to give > ht tp://msdn.microsoft.com/en-us/library/office/ff192913.aspx (How to: Send Information to the Clipboard); which is still as new but it is a redirect 2022 htt ps://learn.microsoft.com/en-us/office/vba/access/Concepts/Windows-API/send-information-to-the-clipboard there as new, and captures first start at Sep 2023 ??
An old capture from Oct 2013 – htt ps://web.archive.org/web/20131003213600/http://msdn.microsoft.com/en-us/library/office/ff192913.aspx
> htt p://msdn.microsoft.com/en-us/library/office/ff194373.aspx (How to: Retrieve Information from the Clipboard). Its dated 2022 currently as redirect htt ps://learn.microsoft.com/en-us/office/vba/access/Concepts/Windows-API/retrieve-information-from-the-clipboard
A archive org from 2013 htt ps://web.archive.org/web/20130113075556/http://ms dn.microsoft.com/en-us/library/office/ff194373.aspx
Maybe this was Rory’s cheat sheet from about 2015 ht tps://www.spreadsheet1.com/how-to-copy-strings-to-clipboard-using-excel-vba.html# VBA does not offer a clipboard object, although Visual Basic 6 did. …. copies just two questions marks to the clipboard when used under Windows 8 and 10 (as tested in September 2015)

April 2019 https://eileenslounge.com/viewtopic....249755#p249755 4 multiple Clipboard for multiple cells MISTAKE WRONG Yasser was right
https://eileenslounge.com/viewtopic....249795#p249795 3

Oct 2019 h tt ps://web.archive.org/web/20200806111619/ht tp s://social.msdn.microsoft.com/Forums/en-US/48e8c30c-24ee-458e-a873-a4e6e13f5926/dataobject-settext-and-putinclipboard-sequence-puts-invalid-data-hex-63-characters-in-clipboard?forum=isvvba

Dec 2019 http://eileenslounge.com/viewtopic.php?p=262011#p262011 1
Dec 2019 http://eileenslounge.com/viewtopic.php?f=18&t=33834 2 Clipboard quote multi line cell text Syntax in Windows clipboard, to Paste multi lines in Excel

08/19/2020 h t tps://web.archive.org/web/20220922124033/h t tps://learn.microsoft.com/en-us/windows/win32/dataxchg/clipboard-formats Clipboard Formats


Jan 2021 https://eileenslounge.com/viewtopic....279659#p279659 1 split clipboard

Jun 2021 https://www.eileenslounge.com/viewto...295816#p295816 1
https://www.eileenslounge.com/viewto...295780#p295780 2 put Text file in Excel Clipboard wonder

Apr 2022 https://www.eileenslounge.com/viewto...294721#p294721 2 Clipboard Convert vertical to horizontal on multiple columns 26 Apr 2022 lost appendix

Jun 2022 https://www.eileenslounge.com/viewto...296145#p296145 2 Power shell not working speakeasy Clipboard first 100 lines text file PowerShell Clipboard
June 2022 https://www.eileenslounge.com/viewto...296126#p296126 3 PowerShell text set clipboard SpeakEasy works not me (snb CreateObject scripting.filesystemobject .opentextfile .readall )

Notes mostly originating from a bomb shell dropped by Mike, (SpeakEasy). The Windows Clipboard


_ - - - - - - -
Nov 2022 https://www.eileenslounge.com/viewto...301028#p301028 3 Office Windows Clipboard
Nov 2022 https://www.eileenslounge.com/viewto...300947#p300947 1 Mike we only have 1 windows clipboard …… just the Windows clipboard. The Office clipboard is just a specific view ….. the clipboard is owned at any particular point in time by the window (or task) that last placed data in the clipboard. In conjunction with this, an application can request that data it is putting on the clipboard use something called 'deferred render' mode - this means that the data is not actually put on the clipboard at that time, the intended purpose being related to performance - it is only provided when a request to paste the data is made. Data marked for deferred render is removed from the clipboard when the owning window is closed. Excel seems to mark quite a lot of data for deferred rendering .
For a range Excel puts about 30 different formats of the data on the clipboard in the following order https://i.postimg.cc/yJn7hGWq/excelclipboarformats.jpg Attachment 5816 https://i.postimg.cc/Kkzx05g1/excelclipboarformats.png The 'Office clipboard' and 'Excel clipboard' are just limited viewers of the Windows clipboard. As indicated above, the source application has the responsibility of putting all the formats it deems necessary onto the clipboard. The consuming app iterates that list of formats to see which ones it supports.
so what’s this then https://www.eileenslounge.com/viewto...300955#p300955 4
so any application can monitor (and display to the best of their ability) the contents of the clipboard*. (* this, by the way, is why you can find a plethora of applets that purport to show the contents of the clipboard such as FreeClipView and ClipDiary (back in the day - XP - Microsoft had their own proper clipboard viewer, but they got rid of it; I suspect because it used the slightly unstable clipboard viewer chain mentioned above; the replacement in Windows 10, which only works if you turn on clipboard history - yes they added a history capability to the OS - lacks features ...)) …. This used to be by inserting a clipboard viewer into the clipboard viewer chain - but this is not as robust as it might be, and is the source of some of the 'bugs' that you refer to (e,g. if applications fail to maintain the clipboard viewer chain properly or if a window in the clipboard viewer chain stops responding to messages). The current best practice method is to create a Clipboard Format Listener, which then receives notifications every time the clipboard changes. Applications are free to display the current contents of the clipboard that they understand in order to show the user what is available to paste. And that's what the Office clipboard is: simply a bespoke view of the Windows clipboard, not an actual separate clipboard. There IS a minor wrinkle to this - the Office clipboard viewer maintains a history of clipboard entries (the clipboard itself can only ever hold one entry). Again without going into the nitty gritty, this history consists of data formats that the Office applications know how to render. This can cause some minor inconsistencies..
something somewhere is telling the clipboard - Indeed. Specifically, the WM_RENDERFORMAT message - Sent to the clipboard owner if it has delayed rendering a specific clipboard format and if an application has requested data in that format. The clipboard owner must render data in the specified format and place it on the clipboard by calling the SetClipboardData function.
The .Copy method puts a copy of the object you are referencing onto the clipboard, in this case a Range.- and then synthesizes all the supported formats. including the Text format (which it does as the synthesized CF_TEXT format, which always ends CR LF [in fact it is even more clever than that, in that it actually separates Rows with CR LF and columns by Tab])
The DataObject's .SetText method exposed to VBA, on the other hand, expects a straightforward text string
(Oh, and one other thing: .PutInClipboard can perform ... unexpectedly ... on occasion if Windows file explorer is open.)
https://www.eileenslounge.com/viewto...301028#p301028 14
Nov 2022 https://www.eileenslounge.com/viewto...301028#p301028 6 the windows clipboard ….. there is no such thing as an Excel Clipboard, and not even anything that might be considered as similar. _____ This thing, Let Application.CutCopyMode = False , is often said to clear the Excel Clipboard.
How about: It takes it out of the (Windows) Clipboard. So if you do a .Paste nothing will happen, as the (Windows) Clipboard is now empty, and the stupid thing is not as clever as that “Office thing that is similar to a clipboard” (that thing what we might call, for want of a better name, the Office Clipboard) , so it wont remember what was copied before. I note that Let Application.CutCopyMode = False makes the clipboard symbol go from orange to white. So maybe that is an indication of if the (Windows) Clipboard is occupied or not._.____________________________
That “Office thing that is similar to a clipboard” (that thing what we might call, for want of a better name, the Office Clipboard) , seems to hold a copied range including the format, long after other things have been copied , even if Let Application.CutCopyMode = False was used a few times, and even if the workbook from which it was copied has long since been closed. In fact, close Excel, go into Word, take a peek into the Office thing that is similar to a clipboard” (that thing what we might call, for want of a better name, the Office Clipboard) there, and you can also pick that copied Excel range out, and paste that copied Excel range into word. But now things are getting a bit inconsistent and quirky: I am finding that sometimes it will give me the formats and sometimes not, - Oh dear, Microsoft got their clipboard viewer chain in a tangle I expect._.____________________
I have often regarded the .Paste as using the Windows Clipboard, whereas I regarded the Excel .PasteSpecial function as using the Excel Clipboard.
Maybe .Paste is using the (Windows) Clipboard whereas the Excel .PasteSpecial function is just some coding to help you choose which version of the copy in the (Windows) Clipboard that you use.
_ - - - - - - - -

Dec 2022 https://www.eileenslounge.com/viewto...301534#p301534 2 Clipboard Trim Eval Range
https://www.eileenslounge.com/viewto...301761#p301761 2 Hans knatsch

Feb 2023 https://www.eileenslounge.com/viewto...304976#p304976 2 Do While Clipboard Gather unique values in one row for each unique key

May 2023 https://www.eileenslounge.com/viewto...307826#p307826 2 clipboard ramble

Jan 2023 https://www.eileenslounge.com/viewto...303007#p303007 2 Clipboard opps a vbCr & vbLf OopsAvbCr&vbLf.xls
Jan 2023 https://www.eileenslounge.com/viewto...303039#p303039 2
The .Copy method puts a copy of the object you are referencing onto the clipboard, in this case a Range.- and then synthesizes all the supported formats. including the Text format (which it does as the synthesized CF_TEXT format , which always ends CR LF [in fact it is even more clever than that, in that it actually separates Rows with CR LF and columns by Tab])
The DataObject's .SetText method exposed to VBA, on the other hand, expects a straightforward text string
The .Copy is going to tell the Clipboard that it …. ( it being VBA I think in this case, or possibly Excel as the controlling/"owning thing") …. this it is in charge of the clipboard for the time being. This it is a deferred render entry thing, but if and when it does actually put stuff in The ( Microsoft Windows ) Clipboard, then in the case of a range it is gonna be quite a few formats, (maybe about 30 ) or maybe not?: I mean, as its a deferred render entry thing it might decide to put different things in The ( Microsoft Windows ) Clipboard depending on what is done later to trigger the thing to do what it is deferring doing when it’s the controlling/ "owning thing" ?
So that is maybe something close to what the .Copy is doing.
.SetText and .PutInClipboard
( I am not sure if this is a deferred entry thing or not ? ).
This can’t do anything much other than put text somewhere since that is all you give it: You give it a simple text string. That’s it.
_.___________________________________________I am not 100% sure what you are saying by your reference synthesized CF_TEXT format.
This is my guess as to what you are saying. :
If I use the .GetFromClipboard and .GetText() from within VBA, then what I get will depend on what was the controlling "owning" thing of the The ( Microsoft Windows ) Clipboard at the time ….
Let me consider the 2 cases relevant here…
_ case1
If .Copy was the owning controlling thing of The ( Microsoft Windows ) Clipboard, then, when it sees the request of .GetFromClipboard from within VBA, it may not quite have the format requested. ….I am trying to understand that link you gave ….. I will take a guess that what is going on in this case1 is this:
It takes one of the formats on the first column from that link you gave which it does have* (*or will have – its deferred entry ....) . It than either makes one of the formats on the second column from that link, or maybe makes one that is not listed there. I don’t know. I do know, and agree with this bit that you said…. it actually separates Rows with CR LF and columns by Tab…..
_ case2
If the DataObject is the owning controlling thing of The ( Microsoft Windows ) Clipboard, ( in other words you did the .SetText and .PutInClipboard thing to put text in The ( Microsoft Windows ) Clipboard) , then …..you put a text in and you will get with the .GetFromClipboard and .GetText() the same text back. Simple as that. ( I am still not sure what of the CF_ things are used though in this case either )











Clsid
https://eileenslounge.com/viewtopic....314950#p314950 Mike GUID list CLSIDs skids SCIDs
https://eileenslounge.com/viewtopic....289020#p289020 2 GUIDS CLSIDs in the { } as in CreateObject("New:" & “{aksjjfhaskj}” & "") Oct 2021
https://eileenslounge.com/viewtopic....286708#p286708 3 CLSID .NET 3.5
https://eileenslounge.com/viewtopic....289020#p289020 2 clsid Oct 2021 Feb 2016
https://chandoo.org/forum/threads/cl...6/#post-223256



API alternative
2012 Looks like first report of it https://web.archive.org/web/20200806...d?forum=isvvba
opened a support case with Microsoft as suggested by Kirk Beller…..MS determined the cause to be 'most likely a flaw in our product'. So far, I have had no notification of when or if it might be fixed - MS helped me develop a robust workaround using native Windows API calls instead of a DataObject. These are documented in the following articles:
> http://msdn.microsoft.com/en-us/libr.../ff192913.aspx (How to: Send Information to the Clipboard); - Cant find on archive, but the original gets redirected to a new one from 2022, archived at similar date https://web.archive.org/web/20240421...-the-clipboard
> http://msdn.microsoft.com/en-us/libr.../ff194373.aspx (How to: Retrieve Information from the Clipboard). – original archived
2013 https://web.archive.org/web/20130113.../ff194373.aspx The original gets redirected to one from 2022 , archived at a similar date https://web.archive.org/web/20221203...-the-clipboard
So far no MS article does the 64bit even though the original article has a post 2019 telling about it
2015 https://www.spreadsheet1.com/how-to-...xcel-vba.html# VBA does not offer a clipboard object, although Visual Basic 6 did. …. copies just two questions marks to the clipboard when used under Windows 8 and 10 (as tested in September 2015)
2015 https://wellsr.com/vba/2015/tutorial...d-paste-clear/ Mac as well like Rory’s 2018 Update: With the rollout of Windows 8 and Windows 10, this solution no longer works reliably,
2018 https://web.archive.org/web/20191220...-the-clipboard difference between the DataObject and the Clipboard
2018 https://chandoo.org/forum/threads/cl...6/#post-223256 Masterbasdor API Post
Mar 2019 My last post is best, https://eileenslounge.com/viewtopic....247809#p247809 , giving further links
Rory’s Tools https://eileenslounge.com/viewtopic....246708#p246708

---

This is post #544
https://www.excelfox.com/forum/showt...ll=1#post24117
https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-pasting-Cliipboard-issues?p=24117&viewfull=1#post24117
https://www.excelfox.com/forum/showt...ge55#post24117
https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-pasting-Cliipboard-issues/page55#post24117

---

Some notes to bring together some "Clsid Clipboard" discussions from approximately here https://eileenslounge.com/viewtopic....288963#p288963 and here, https://eileenslounge.com/viewtopic....314925#p314925

Clsid Late Binding

These sort of things:
MSForms.ListBox https://web.archive.org/web/20140610...emory-listbox/
CreateObject("New:{8BD21D20-EC42-11CE-9E0D-00AA006002F3}")

MSForms.DataObject https://web.archive.org/web/20140610...ms-dataobject/
CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

The last one is the most commonly used and known about, at least around the VBA area where I am, or have been, over the last few years

Some notes picked up from smarter people
Mike (SpeakEasy), Hans, Chris : From about here https://eileenslounge.com/viewtopic....288963#p288963
Microsoft simply hasn't exposed a class name for DataObject. Most object we use do have a class name. For those we can use early binding by setting a reference, or late binding by using GetObject/CreateObject with a class name. DataObject is an exception, so we MUST use its hexadecimal ClassID.
36-character strings: .. It's the formal string representation of the underlying 128bit UUID (which Microsoft tend to refer to as a GUID; a CLSID is just a GUID being used for a specific role - identifying a class object), that representation being 32 hexadecimal digits separated by 4 hyphens ...
We humans are sheltered from them normally, but there are odd occasions where we find we have to use them. Not very often for most users, it has to be said.

Mike (SpeakEasy), from about here https://eileenslounge.com/viewtopic....314925#p314925
GUID is just a unique identifier. Microsoft use unique identifiers for all sorts of purposes in Windows, and then they are given specific names to better indicate their purpose, hence FMTID in my previous post. The GUIDs you can use with CreateObject are actually knows as CLSIDs (or Class IDs); each and every class on Windows has it's own CLSID, and you can look them all up in the registry under HKEY_CLASSES_ROOT.
In general it offers little benefit compared to using the ProgID (the human readable string, such as word.application), and indeed often obfuscates what is going on (malware writers were big fans ...).

There are, however, a few rare scenarios where it can prove useful. Some COM objects do not have a ProgID. Often such objects are not directly useful to VB/VBA programmers, but some are. The one you use in your example, 1C3B4210-F441-11CE-B9EA-00AA006B1A69, is the userform clipboard object*, so we get a cheap way of simple clipboard access.
( * More accurately the userform dataobject, (Microsoft.Vbe.Interop.Forms.DataObjectClass - this is NOT a ProgID) which provides simple access to the clipboard )

---

Here is a macro I found a few years back:

Code:

---

'                            This workbook kept and updated here:  (Folder at appBox.com excel fox2 excelfox2@gmail.com  RegistryCmdListsWinGimics)
' https://powershell.one/wmi/root/cimv2/stdregprov-EnumKey    '  https://www.vbforums.com/showthread.php?552899-Getting-all-sub-keys-from-a-registry-value                  https://www.vba-tutorial.de/apireferenz/registry.htm
Sub ListCLSIDs() '  http://www.eileenslounge.com/viewtopic.php?f=26&t=22603&p=289007#p289007
Dim Ws As Worksheet: Set Ws = Me ' Set Ws = ActiveSheet
Dim Registry As Object, varKey As Variant, varKeys As Variant
 Set Registry = GetObject("winmgmts:\\.\root\default:StdRegProv")
 Registry.EnumKey 2147483650#, "SOFTWARE\Classes\CLSID", varKeys ' https://powershell.one/wmi/root/cimv2/stdregprov-EnumKey
Dim Cnt As Long: Let Cnt = 1
    For Each varKey In varKeys
'    Let Ws.Range("A" & Ws.Range("A" & Ws.Rows.Count & "").End(xlUp).Row + 1 & "").Value = varKey
 Let Cnt = Cnt + 1
    Let Ws.Range("A" & Cnt & "").Value = varKey
    Next
End Sub

---

Here is a more recent one from Mike ( Speakeasy https://eileenslounge.com/viewtopic....314941#p314941 )

Code:

---

Public Const HKEY_CLASSES_ROOT = &H80000000  '  https://eileenslounge.com/viewtopic.php?p=314945#p314945
Public Sub GetCLSIDs_and_ProgIDs() '  https://eileenslounge.com/viewtopic.php?p=314941#p314941

    Dim entryArray() As Variant
    Dim KeyValue  As Variant  '  Dim KeyValue  As String  ..... Automation-errors-The-called-object-has-been-disconnected-from-the-clients - https://eileenslounge.com/viewtopic.php?p=314950#p314950    Mike: The error is somewhat misleading. It is down to the fact that the XP (and presumably Vista and W7, although I can't test them) WMI provider (which gives us the Registry access) handles returning Null differently than the one on W10  https://eileenslounge.com/viewtopic.php?p=314953#p314953
    Dim KeyPath As String
    Dim x As Long
    Dim row As Long
   
    Dim RegistryObject As Object
    Dim strComputer As String
   
    strComputer = "."
    Set RegistryObject = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\default:StdRegProv")
    RegistryObject.EnumKey HKEY_CLASSES_ROOT, "CLSID", entryArray
   
    ActiveSheet.Cells(1, 1).Value = "CLSID"
    ActiveSheet.Cells(1, 2).Value = "ProgID"
   
    row = 2
    For x = 0 To UBound(entryArray)
        RegistryObject.getstringvalue HKEY_CLASSES_ROOT, "CLSID\" & entryArray(x) & "\ProgId", "", KeyValue
        If KeyValue <> "" Then
            ActiveSheet.Cells(row, 1) = entryArray(x)
            ActiveSheet.Cells(row, 2) = KeyValue
            row = row + 1
        End If
    Next x
   
End Sub


---

In post #545 , I slightly rearrange those routines above, ( mine and Mike’s Clsid list thing making codings) , just to make them easier to compare, and then in post #547 I do Some initial comparison thoughts

---

By the way, back then I did myself the coding below to create an object from the Clsid and then look at the TypeName( object ) . I did that as a geuss on how to get the class name, if it had one. More about that abortion Later

Code:

---

' https://powershell.one/wmi/root/cimv2/stdregprov-EnumKey    '  https://www.vbforums.com/showthread.php?552899-Getting-all-sub-keys-from-a-registry-value
Sub CLSIDsValueNames() '
Dim Ws As Worksheet: Set Ws = Me ' Set Ws = ActiveSheet
Dim RngSel As Range:  Set RngSel = Selection
    If RngSel.Cells.Count = 1 Then MsgBox prompt:="Please select at least 2 cells in column A": Exit Sub
    If RngSel.Cells.Columns.Count <> 1 Then MsgBox prompt:="Please select at least 2 cells in only column A": Exit Sub
    If Not RngSel.Cells.Column = 1 Then MsgBox prompt:="Please select at least 2 cells in column A": Exit Sub
Dim stearCel As Range
    For Each stearCel In RngSel
    Let Ws.Range("B" & stearCel.row & "").Value = "Tried" ' To indicate I tried - this can be useful to see where it crashed
    Dim OOPObj  As Object
    On Error GoTo EyeEyeSkipper
    Set OOPObj = CreateObject("New:" & stearCel.Value & "")
    Let Ws.Range("D" & stearCel.row & "").Value = TypeName(OOPObj)
    Let Application.DisplayAlerts = False
    ThisWorkbook.Save ' This is done to save all got so far incase Excel crashes on next loop or below
    Let Application.DisplayAlerts = True
    On Error Resume Next
    OOPObj.Close
    On Error GoTo 0
    Set OOPObj = Nothing
EyeEyeSkipper:
    On Error GoTo -1
    On Error GoTo 0
    Next stearCel
End Sub


---

These next couple of codings are mine and Mike’s, as in the last post, but I have just rearranged them a bit and made very minor changes, just to make it a bit easier to compare.

Code:

---

                                                                                  'Public Const HKEY_CLASSES_ROOT = &H80000000  '  https://eileenslounge.com/viewtopic.php?p=314945#p314945      https://www.devhut.net/enumerating-registry-subkeys-using-wmi-in-vba/
Public Sub ListCLSIDs_and_ProgIDs_Mike()  ' Mike        '  https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-pasting-Cliipboard-issues?p=24128&viewfull=1#post24128
Dim Ws As Worksheet:  Set Ws = ThisWorkbook.Worksheets("CLSIDsOldVista4810TZG")        ' Set Ws = Me '

Dim RegObject As Object, entryArray() As Variant, KeyValue As Variant            ' Dim KeyValue  As String ' Mike .... error is somewhat misleading. It is down to the fact that the XP (and presumably Vista and W7, although I can't test them) WMI provider (which gives us the Registry access) handles returning Null differently than the one on W10. Simply change the declaration of KeyValue from  Dim KeyValue As String  to  Dim KeyValue As Variant  https://eileenslounge.com/viewtopic.php?p=314953#p314953
                                                                                      '  Dim KeyPath As String ' - ??
                                                  Dim strComputer As String: Let strComputer = "."
 Set RegObject = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\default:StdRegProv")
 RegObject.EnumKey &H80000000, "CLSID", entryArray()                            ' HKEY_CLASSES_ROOT, "CLSID", entryArray()
                                                                                                                ' Let Ws.Cells(1, 6).Value = "CLSID": Let Ws.Cells(1, 7).Value = "ProgID"
Dim ExRowCnt As Long ' ================================================================================================
    For ExRowCnt = 0 To UBound(entryArray())
    RegObject.getstringvalue &H80000000, "CLSID\" & entryArray(ExRowCnt) & "\ProgId", "", KeyValue
   
    Let Ws.Range("F" & ExRowCnt + 2 & "") = entryArray(ExRowCnt)
    Let Ws.Range("G" & ExRowCnt + 2 & "") = KeyValue
    Next ExRowCnt ' ===================================================================================================
End Sub


---

Code:

---

Public Sub ListCLSIDs_Me()              '  Me              http://www.eileenslounge.com/viewtopic.php?f=26&t=22603&p=289007#p289007      ' https://powershell.one/wmi/root/cimv2/stdregprov-EnumKey    '  https://www.vbforums.com/showthread.php?552899-Getting-all-sub-keys-from-a-registry-value                  https://www.vba-tutorial.de/apireferenz/registry.htm
Dim Ws As Worksheet: Set Ws = ThisWorkbook.Worksheets("CLSIDsOldVista4810TZG")                    ' set ws = Set Ws = Me ' Set Ws = ActiveSheet

Dim RegObject As Object, KeyValues() As Variant, KeyValue As Variant
 
 
 Set RegObject = GetObject("winmgmts:\\.\root\default:StdRegProv")
 RegObject.EnumKey 2147483650#, "SOFTWARE\Classes\CLSID", KeyValues()            ' https://powershell.one/wmi/root/cimv2/stdregprov-EnumKey

Dim ExRowCnt As Long ' ================================================================================================
    For Each KeyValue In KeyValues()
 
    Let ExRowCnt = ExRowCnt + 1
    Let Ws.Range("A" & ExRowCnt + 1 & "") = KeyValue
   
    Next          ' ===================================================================================================
End Sub
'  https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-pasting-Cliipboard-issues?p=24127&viewfull=1#post24127


---

https://i.postimg.cc/FRxkK7Wd/Me-Cls...s-Prog-IDs.jpg
https://i.postimg.cc/FRxkK7Wd/Me-Cls...s-Prog-IDs.jpg____

Later
Post #546 https://www.excelfox.com/forum/showt...ll=1#post24137
https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-pasting-Cliipboard-issues?p=24137&viewfull=1#post24137
https://www.excelfox.com/forum/showt...ge55#post24137
https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-pasting-Cliipboard-issues/page55#post24137

---

My object creating abortion
In the coding of mine, ( Public Sub ListCLSIDs_Me() or Sub ListCLSIDs() ) , I only got the list of the 36 characters in the squiggly bracket , { } things. ( I did not know for sure how to get the name )
But I was interested in the name thing. So as I was not sure how to do it, I wrote , back then, that second program, Sub CLSIDsValueNames() to try and get that name somehow. It basically creates the object, or tries to, and if it is successful, it tries to get the TypeName( object ) of that object.
It works just on the selection you make in column A, the 36 characters in the squiggly bracket , { } things.
Back then, I did a few . but, I broke off from doing it for all the 6527 things, as after doing a few, some applications started appearing after a computer restart, and I had to tank them from the Task Manager.
But for the ones I did do so far, there is some similarity between what I get for my
____ = TypeName( the created object )
, and what Mike effectively gets from these 2 lineyMike’s
RegObject.getstringvalue &H80000000, "CLSID" & entryArray(ExRowCnt) & "\ProgId", "", KeyValue
Prog = KeyValue

Here, just as example, some of the first results
https://i.postimg.cc/2jGKt0dz/Mine-a...al-results.jpg
https://i.postimg.cc/htkym6n0/Mine-a...al-results.jpg
https://i.postimg.cc/ncwwFMwg/Mine-a...al-results.jpg
https://i.postimg.cc/tgFrNkCG/Mine-a...al-results.jpg
https://i.postimg.cc/76DKszfG/Mine-a...al-results.jpg
https://i.postimg.cc/76DKszfG/Mine-a...al-results.jpg

Check out also the worksheet named CLSIDsOldVista4810TZG in this file
CLSDsUndClassNames.xls - https://app.box.com/s/nkjwti5yym9j0v634hrxerz4x7n1o90w

Here again that abortion of a coding

Code:

---

Sub CLSIDsValueNames() '
Dim Ws As Worksheet: Set Ws = Me ' Set Ws = ActiveSheet
Dim RngSel As Range:  Set RngSel = Selection
    If RngSel.Cells.Count = 1 Then MsgBox prompt:="Please select at least 2 cells in column A": Exit Sub
    If RngSel.Cells.Columns.Count <> 1 Then MsgBox prompt:="Please select at least 2 cells in only column A": Exit Sub
    If Not RngSel.Cells.Column = 1 Then MsgBox prompt:="Please select at least 2 cells in column A": Exit Sub
Dim stearCel As Range
    For Each stearCel In RngSel
    Let Ws.Range("B" & stearCel.row & "").Value = "Tried" ' To indicate I tried - this can be useful to see where it crashed
    Dim OOPObj  As Object
    On Error GoTo EyeEyeSkipper
    Set OOPObj = CreateObject("New:" & stearCel.Value & "")
    Let Ws.Range("D" & stearCel.row & "").Value = TypeName(OOPObj)
    Let Application.DisplayAlerts = False
    ThisWorkbook.Save ' This is done to save all got so far incase Excel crashes on next loop or below
    Let Application.DisplayAlerts = True
    On Error Resume Next
    OOPObj.Close
    On Error GoTo 0
    Set OOPObj = Nothing
EyeEyeSkipper:
    On Error GoTo -1
    On Error GoTo 0
    Next stearCel
End Sub

---

Some initial comparison thoughts

Here again the two macros in their slightly rearranged and modified form to help compare
https://i.postimg.cc/rskdx3bT/Me-Clsids.jpg https://i.postimg.cc/VN20LdWd/Me-Cls...s-Prog-IDs.jpg https://i.postimg.cc/RC7J4C6Z/Mike-Clsids-Prog-IDs.jpg
https://i.postimg.cc/QBkdfNP3/Me-Cls...s-Prog-IDs.jpg Attachment 5814
https://i.postimg.cc/VN20LdWd/Me-Cls...s-Prog-IDs.jpg
https://i.postimg.cc/FRxkK7Wd/Me-Cls...s-Prog-IDs.jpg

The registry object looks the same. Its Getted as an object like these
Mike _ "winmgmts:{impersonationLevel=impersonate}!" & strComputer & "\root\default:StdRegProv"
Me ___________________ "winmgmts:\\.\root\default:StdRegProv"

My code I cobbled together a bit wildly. I am not sure yet how Mike got his

We both do a .EnumKey thing referencing the CLSID which gives us an array back of a Variant types, (0 to 6525) . I use a variable KeyValues() , Mike uses a variable entryArray() . Those arrays are identical , perhaps as expected as the FONT=Courier New].EnumKey [/FONT] thing is very similar
___ .EnumKey &H80000000, "CLSID", entryArray() - Mike
.EnumKey 2147483650#, "SOFTWARE\Classes\CLSID", KeyValues() - Me

The array ( Me - KeyValues() , Mike entryArray() ) has exclusively strings in it, the first is just the word CLSID ,the rest of the 6525 are those 36 characters in the squiggly bracket , { } things.
We both effectively loop them strings out. I do just that. Mike makes that the first column that, ( and also Mike makes a second column, which appears to get the name you would use in the late binding thing CreateObject("name") thing. )

Squiggly bracket , { } thing
Regarding that thing in the Mike first of two columns, (the only thing my first macro does), so it is that 36 characters in the squiggly bracket , { } things. I ended up putting each squiggly bracket , { } thing in a variable KeyValue, getting each one out of my KeyValues() . Mike just loops out directly each of his entryArray() (He does in his original coding give that the heading, CLSID

The name you would use in the late binding thing CreateObject("name") thing.
My second abortion coding, Sub CLSIDsValueNames() , attempted to get that. It did not work too well, but got something similar sometimes.
Mike gets that as well in his coding.
( Mike strangely uses the variable name KeyValue , for the name thing. Whereas I use the variable name KeyValue for the other thing, the squiggly bracket , { } things, which Mike uses no variable for but they comes from his entryArray() and he gives his first column the name CLSID . It is a bit pedantic perhaps, but I might like to think a bit more about the better more appropriate variable name as it can help understand/ remember what is going on **)
Mike gets the name effectively from a line each time inside the loop
.getstringvalue &H80000000, "CLSID" & entryArray(ExRowCnt) & "\ProgId", "", KeyValue


**Maybe one or both of us could do better with the name of our variables to make things more clear ??

Maybe later thoughts, …… later ….
For now I will post here , at eileenslounge, and wait a while, in case someone Smarter has any comments.
https://eileenslounge.com/viewtopic....316704#p316704
https://eileenslounge.com/viewtopic....316705#p316705







( A few other for later, later thoughts ….._
_..... there is one obvious similarity in the two macros, they are using some WimMIe widget gismo thing. This WimMIe widget gismo stuff is a windows lurking thing so I expect you can get in a few ways. I feel a desire to do this sort of thing with PowerShell from within VBA. I’m not sure why yet , - either a simple perversion or some gut feeling that it might be handy later …. Not quite sure why yet … just a feeling in my bones that it could be useful )

---

Share ‘CLSDsUndClassNames.xls’ https://app.box.com/s/nkjwti5yym9j0v634hrxerz4x7n1o90w

This is post https://www.excelfox.com/forum/showt...ge54#post24118
https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-pasting-Cliipboard-issues/page54#post24118

---

Another look at the abortion , 2024

I decided to risk it and went through all the squiggly bracket , { } things in the macro that tries to make an object from them like CreateObject("New:" & squiggly bracket { } thing & "")
It wasn’t so bad, - no new strange things appeared permanently
There were a few crashes which I either let Excel restart itself or I tanked the crashed Excel with the Task Manage r and restarted. ( It happened so much that I got the thing you get after a lot of crashes https://i.postimg.cc/t4w5CTYY/Wir-be...e-Crashing.jpg )
A few other things I noticed.
There were a few hangs, not many
The OLE wait thing ( https://i.postimg.cc/Vkdg8Msx/OLE-waiting.jpg ) popped up few times. Hitting the OK usually moved it on. Occasionally once was enough but 3 times was most commonly required
Excel 2003 tried to install or configure a few times.
Some strange Xceed Encryption Library also appeared a few times https://i.postimg.cc/NGkp7BPz/Xceed-...on-Library.jpg

---

I was not quite so brilliant with VBA coding as I am now back when I wrote that first abortion coding, and also I thought it might be good to get some error messages. So I did another

Code:

---

Sub CLSIDsValueNames2() ' https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-pasting-Cliipboard-issues/page54#post24118
Dim Ws As Worksheet: Set Ws = Me ' Set Ws = ActiveSheet
Dim RngSel As Range:  Set RngSel = Selection
'    If RngSel.Cells.Count = 1 Then MsgBox prompt:="Please select at least 2 cells in column A": Exit Sub
    If RngSel.Cells.Columns.Count <> 1 Then MsgBox prompt:="Please select only in column A": Exit Sub
    If Not RngSel.Cells.Column = 1 Then MsgBox prompt:="Please select at least 1 cell in column A": Exit Sub
Dim stearCel As Range
    For Each stearCel In RngSel
    Let Ws.Range("D" & stearCel.Row & "") = ""
    Let Ws.Range("B" & stearCel.Row & "") = "Tried" ' To indicate I tried - this can be useful to see where it crashed
    Dim OOPObj  As Object
    On Error GoTo Bed
    Set OOPObj = CreateObject("New:" & stearCel.Value & "")
    On Error GoTo 0
    On Error GoTo Bed2
    Let Ws.Range("D" & stearCel.Row & "") = Ws.Range("D" & stearCel.Row & "") & TypeName(OOPObj)
    On Error GoTo 0
Skipper:
    On Error Resume Next
    OOPObj.Close
    On Error GoTo 0
    Set OOPObj = Nothing
    Next stearCel
Exit Sub
Bed:  '    Error on create object
 Let Application.DisplayAlerts = False
 ThisWorkbook.Save ' This is done to save all got so far in the case of an error
 Let Application.DisplayAlerts = True
 Let Ws.Range("D" & stearCel.Row & "") = "Error on creat object    " & Err.Description & "  " & Err.Number
Resume Next
Bed2: '    Error on TypeName
 Let Ws.Range("D" & stearCel.Row & "") = Ws.Range("D" & stearCel.Row & "").Value & "  Error on type name    " & Err.Description & "  " & Err.Number
On Error GoTo -1
On Error GoTo 0
GoTo Skipper
End Sub

Sub SpeakEasy()
    Dim objIE As Object
    Set objIE = GetObject("new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}")
    MsgBox TypeName(objIE)
End Sub
Sub RegyRead()
Dim key As String, RegRead As String
Let key = "{1C3B4210-F441-11CE-B9EA-00AA006B1A69}"
Dim Ws As Object

Set Ws = CreateObject("WScript.Shell")
RegRead = Ws.RegRead(key)

Set Ws = Nothing

End Sub


---

It tells me the error if the object could not be made (if the CreateObject("New:" & squiggly bracket { } thing & "") line errors ) , ( as well if the object making seemed ok, but then the TypeName(object) errors - that situation occurs rarely)
( Also in column C, I added some extra notes about things that happened. I may be a cell or two out on when it happened, but if that is the case, I mention that I am not sure )
Here are the things that happened
https://i.postimg.cc/BQfvbmK9/OLE-waiting.jpg
https://i.postimg.cc/mgb2Nhjm/One-Note-help.jpg
https://i.postimg.cc/50Z2w3Kz/Other-...v2-0-50727.jpg
https://i.postimg.cc/Kv78MtPK/Progra...-festlegen.jpg
https://i.postimg.cc/pdcTdxDq/Research-task-pane.jpg
https://i.postimg.cc/0Q6NM3nx/Speech-pop-up.jpg
https://i.postimg.cc/SQMsmBjf/Unable...ice-Driver.jpg
https://i.postimg.cc/1Xw5Mqbj/Videoimport.jpg
https://i.postimg.cc/9QJXxVNC/Xceed-...on-Library.jpg




The version thing is inconsistent, not always happening , as are both some of the crashes , and some of the number of times an "OLE" pop up needs to be clicked on to move on . But I think I have a 90% good report of what would happen.

https://i.postimg.cc/wvhhP0yX/Result...lue-Names2.jpg
https://i.postimg.cc/nhPYQsfD/Result...lue-Names2.jpg
https://i.postimg.cc/76v3mp6P/Result...lue-Names2.jpg
Attachment 5822Attachment 5823Attachment 5824
https://i.postimg.cc/wvhhP0yX/Result...lue-Names2.jpg
https://i.postimg.cc/nhPYQsfD/Result...lue-Names2.jpg
https://i.postimg.cc/76v3mp6P/Result...lue-Names2.jpg

Problem getting links to this post https://www.excelfox.com/forum/showt...ge55#Post24128

Problem getting links to this post
https://www.excelfox.com/forum/showt...ge55#Post24139
https://www.excelfox.com/forum/showt...ge55#Post24139

---