TheAntiGates
08-11-2017, 05:06 AM
If you can tell me whether this is useful or wasteful, I'm swimming above my pay grade here so straighten me out. After some experimenting I determined that you can (only partially) "simulate" Excel's copy buffer via the Windows Clipboard using its "XML Spreadsheet" format thereof. This is wildly limited so I'm not going to receive a Congressional Medal of Honor, but maybe it might help or inspire someone usefully.
Before going any further, this may be bombing an anthill. Maybe there's a vastly simpler way to get the clipboard contents, such as with DataObj. Take this FWIW.
I modified Leigh Webber's sometimes popularly cited code (link below) for retrieving and restoring Windows clipboard's Rich Text content (such as you might have if you copied a multi-colored passage in Word), via API calls, modified to instead use the XML Spreadsheet format. The result is that I was able to
- Select a rectangle of cells on a worksheet, and Ctrl-C.
- Go off somewhere else - to another sheet, then another program.
- Run code to store the XML representation of Windows clipboard in a string variable.
- Copy some random Notepad text into the Windows copy buffer just to keep me honest (wiping out the clipboard).
- Restore the Windows copy buffer from my string.
- Hit Control-V on a different Workbook (or even a different instance),
and I got all the contents copied, and basic formatting, including pattern and borders.
I did NOT pick up conditional formatting. I did not even pick up formulae.
Maybe someone could confirm this but it appears that all I essentially achieved was to pick up exactly what gets transferred when you open two instances of Excel (the same version, or different versions) and copy/paste a range from one to the other. Values but no formulae get copied when you do that, for example.
This is not that big a deal. However if someone has an application where they could benefit from the limited "XML" content accessible from the Windows clipboard, this is one way. One might think, well, they could just save off the Selection range, and they'd have EVERYTHING that an Excel copy and paste gets (like, the formulae, duh!). I suppose so. Yet perhaps there's some scenario where
- cells had been copied into the windows clipboard,
- they're no longer in the Office clipboard,
- they're no longer selected, and
- just the values and maybe basic format are desired. (and the cell sequence and row/column orientation)
In that scenario, it's really tough (if not impossible?) to programatically know "what" has been copied, so this would query it from the Windows clipboard.
Perhaps in that or another scenario where multiple instances of Excel are running this could be useful. Or I could be just high in this approach. (For one thing, I don't know exactly how much of this can be done with DataObject.) If you can tell me whether this idea is useful or wasteful, I'm swimming above my pay grade here so straighten me out.
Webber article
A clipboard object for VBA, including Microsoft Word
https://social.msdn.microsoft.com/Forums/office/en-US/ee9e0d28-0f1e-467f-8d1d-1a86b2db2878/a-clipboard-object-for-vba-including-microsoft-word?forum=worddev
Simplified (text) version of the process:
Retrieve Information from the Clipboard
https://msdn.microsoft.com/en-us/vba/access-vba/articles/retrieve-information-from-the-clipboard
Chip, on DataObject:
Using The Clipboard In VBA
http://www.cpearson.com/excel/clipboard.aspx
Before going any further, this may be bombing an anthill. Maybe there's a vastly simpler way to get the clipboard contents, such as with DataObj. Take this FWIW.
I modified Leigh Webber's sometimes popularly cited code (link below) for retrieving and restoring Windows clipboard's Rich Text content (such as you might have if you copied a multi-colored passage in Word), via API calls, modified to instead use the XML Spreadsheet format. The result is that I was able to
- Select a rectangle of cells on a worksheet, and Ctrl-C.
- Go off somewhere else - to another sheet, then another program.
- Run code to store the XML representation of Windows clipboard in a string variable.
- Copy some random Notepad text into the Windows copy buffer just to keep me honest (wiping out the clipboard).
- Restore the Windows copy buffer from my string.
- Hit Control-V on a different Workbook (or even a different instance),
and I got all the contents copied, and basic formatting, including pattern and borders.
I did NOT pick up conditional formatting. I did not even pick up formulae.
Maybe someone could confirm this but it appears that all I essentially achieved was to pick up exactly what gets transferred when you open two instances of Excel (the same version, or different versions) and copy/paste a range from one to the other. Values but no formulae get copied when you do that, for example.
This is not that big a deal. However if someone has an application where they could benefit from the limited "XML" content accessible from the Windows clipboard, this is one way. One might think, well, they could just save off the Selection range, and they'd have EVERYTHING that an Excel copy and paste gets (like, the formulae, duh!). I suppose so. Yet perhaps there's some scenario where
- cells had been copied into the windows clipboard,
- they're no longer in the Office clipboard,
- they're no longer selected, and
- just the values and maybe basic format are desired. (and the cell sequence and row/column orientation)
In that scenario, it's really tough (if not impossible?) to programatically know "what" has been copied, so this would query it from the Windows clipboard.
Perhaps in that or another scenario where multiple instances of Excel are running this could be useful. Or I could be just high in this approach. (For one thing, I don't know exactly how much of this can be done with DataObject.) If you can tell me whether this idea is useful or wasteful, I'm swimming above my pay grade here so straighten me out.
Webber article
A clipboard object for VBA, including Microsoft Word
https://social.msdn.microsoft.com/Forums/office/en-US/ee9e0d28-0f1e-467f-8d1d-1a86b2db2878/a-clipboard-object-for-vba-including-microsoft-word?forum=worddev
Simplified (text) version of the process:
Retrieve Information from the Clipboard
https://msdn.microsoft.com/en-us/vba/access-vba/articles/retrieve-information-from-the-clipboard
Chip, on DataObject:
Using The Clipboard In VBA
http://www.cpearson.com/excel/clipboard.aspx