Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a variable's contents onto the clipboard
I'd much appreciate one of the experts taking a look at the question I posted here
please. As you see the only solution I've had so far seems astonishingly complex. And, unless it's down to my using it wrongly, it seems rather inflexible too. https://www.excelforum.com/excel-pro...ml#post5258866 Terry, East Grinstead, UK |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a variable's contents onto the clipboard
Terry,
Here's what I've been using since day1 programming; - it's a great tutorial IMO! Working With The Windows Clipboard This page describes various methods in Visual Basic For Applications (VBA) for copying data to and retrieving data from the Windows clipboard. In VBA, you are restricted to setting and retrieving only text data. To copy data directly from a worksheet cell to the Windows clipboard, you can use the COPY method of the Range object, e.g., Range("A1").Copy. However, copying other data to the clipboard, such as variable, cell comments, sheet names, etc, is not as simple as it might be. VBA does not give you generic PutOnClipboard or GetOffClipboard procedures, so we'll create them here. Along the way, we'll look at how VBA does interact with the Windows clipboard. Because these procedures use the DataObject variable type, you must have a reference set in your VBA project to the Microsoft Forms 2.0 object library (FM20.DLL). [Copying To The Clipboard] To access the Windows Clipboard from VBA, you must go through an intermediate object of the DataObject type. If your VBA procedure will be working with the clipboard, declare a NEW DataObject object with the following statement. Dim MyDataObj As New DataObject The SetText method of the DataObject variable is used to store a text string or numeric value in the variable For example: MyDataObj.SetText "This Is A Text String" Or MyDataObj.SetText 123.456 This sets the contents of MyDataObj to a value. To copy the contents of the variable MyDataObj to the Windows clipboard, use the PutInClipboard method . MyDataObj.PutInClipboard [Pasting From The Clipboard] To retrieve the contents of the clipboard, use the following statement: MyDataObj.GetFromClipboard This sets the contents of MyDataObj to the contents of the Windows clipboard. The counterpart to the SetText method is the GetText method. This method returns the contents of DataObject to another variable. For example, Dim MyVar As Variant MyVar = MyDataObj.GetText Using this knowledge, we can create the following VBA procedures: Public Sub PutOnClipboard(Obj As Variant) Dim MyDataObj As New DataObject MyDataObj.SetText Format(Obj) MyDataObj.PutInClipboard End Sub Public Function GetOffClipboard() As Variant Dim MyDataObj As New DataObject MyDataObj.GetFromClipboard GetOffClipboard = MyDataObj.GetText() End Function Public Sub ClearClipboard() Dim MyDataObj As New DataObject MyDataObj.SetText "" MyDataObj.PutInClipboard End Sub I use these formulas quite often to place the formula of the active cell on to the clipboard, to allow cut and paste operations without Excel changing any cell references. You may find it useful to link them to command items on your right click menu. Sub CopyFormula() Dim x As New DataObject x.SetText ActiveCell.Formula x.PutInClipboard End Sub Sub PasteFormula() On Error Resume Next Dim x As New DataObject x.GetFromClipboard ActiveCell.Formula = x.GetText End Sub Clearing The Clipboard To completely clear the clipboard, you need to use a few API calls: Declare Function CloseClipboard Lib "user32" () As Long Declare Function EmptyClipboard Lib "user32" () As Long Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long Sub ClearClipboard() OpenClipboard 0& EmptyClipboard CloseClipboard End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a variable's contents onto the clipboard
Hi Garry,
Thanks, but isn't that the method I described in the post I referenced from the Excel Forum? As I said there, that fails in Windows 10, delivering nothing when pasted externally. Because of that 'bug' or whatever it is, also referenced in that thread. Are you using Win 7 or earlier, under which that method *does* work? Meanwhile as you see I do have the absurdly complex 'Win 8 and later' method working! Best wishes, Terry, East Grinstead, UK ==================== GS wrote: Terry, Here's what I've been using since day1 programming; - it's a great tutorial IMO! Working With The Windows Clipboard This page describes various methods in Visual Basic For Applications (VBA) for copying data to and retrieving data from the Windows clipboard. In VBA, you are restricted to setting and retrieving only text data. To copy data directly from a worksheet cell to the Windows clipboard, you can use the COPY method of the Range object, e.g., Range("A1").Copy. However, copying other data to the clipboard, such as variable, cell comments, sheet names, etc, is not as simple as it might be. VBA does not give you generic PutOnClipboard or GetOffClipboard procedures, so we'll create them here. Along the way, we'll look at how VBA does interact with the Windows clipboard. Because these procedures use the DataObject variable type, you must have a reference set in your VBA project to the Microsoft Forms 2.0 object library (FM20.DLL). [Copying To The Clipboard] To access the Windows Clipboard from VBA, you must go through an intermediate object of the DataObject type. If your VBA procedure will be working with the clipboard, declare a NEW DataObject object with the following statement. Dim MyDataObj As New DataObject The SetText method of the DataObject variable is used to store a text string or numeric value in the variable For example: MyDataObj.SetText "This Is A Text String" Or MyDataObj.SetText 123.456 This sets the contents of MyDataObj to a value. To copy the contents of the variable MyDataObj to the Windows clipboard, use the PutInClipboard method . MyDataObj.PutInClipboard [Pasting From The Clipboard] To retrieve the contents of the clipboard, use the following statement: MyDataObj.GetFromClipboard This sets the contents of MyDataObj to the contents of the Windows clipboard. The counterpart to the SetText method is the GetText method. This method returns the contents of DataObject to another variable. For example, Dim MyVar As Variant MyVar = MyDataObj.GetText Using this knowledge, we can create the following VBA procedures: Public Sub PutOnClipboard(Obj As Variant) Dim MyDataObj As New DataObject MyDataObj.SetText Format(Obj) MyDataObj.PutInClipboard End Sub Public Function GetOffClipboard() As Variant Dim MyDataObj As New DataObject MyDataObj.GetFromClipboard GetOffClipboard = MyDataObj.GetText() End Function Public Sub ClearClipboard() Dim MyDataObj As New DataObject MyDataObj.SetText "" MyDataObj.PutInClipboard End Sub I use these formulas quite often to place the formula of the active cell on to the clipboard, to allow cut and paste operations without Excel changing any cell references. You may find it useful to link them to command items on your right click menu. Sub CopyFormula() Dim x As New DataObject x.SetText ActiveCell.Formula x.PutInClipboard End Sub Sub PasteFormula() On Error Resume Next Dim x As New DataObject x.GetFromClipboard ActiveCell.Formula = x.GetText End Sub Clearing The Clipboard To completely clear the clipboard, you need to use a few API calls: Declare Function CloseClipboard Lib "user32" () As Long Declare Function EmptyClipboard Lib "user32" () As Long Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long Sub ClearClipboard() OpenClipboard 0& EmptyClipboard CloseClipboard End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a variable's contents onto the clipboard
"Terry Pinnell" wrote in message
Hi Garry, Thanks, but isn't that the method I described in the post I referenced from the Excel Forum? As I said there, that fails in Windows 10, delivering nothing when pasted externally. Because of that 'bug' or whatever it is, also referenced in that thread. Are you using Win 7 or earlier, under which that method *does* work? Meanwhile as you see I do have the absurdly complex 'Win 8 and later' method working! Best wishes, Terry, East Grinstead, UK Without registering and logging into the site you cited it's not possible to see any code, yours or suggested solutions, to understand anything about what you're doing other than you've got some sort of problem with clipboard text. As it happens there is a relatively recent bug (from memory surfaced about 3 years ago) that affects the DataObject method with X64. Various solutions have been suggested which appear to work for some, though probably best to use the clipboard APIs. Peter T |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a variable's contents onto the clipboard
"Peter T" wrote:
"Terry Pinnell" wrote in message Hi Garry, Thanks, but isn't that the method I described in the post I referenced from the Excel Forum? As I said there, that fails in Windows 10, delivering nothing when pasted externally. Because of that 'bug' or whatever it is, also referenced in that thread. Are you using Win 7 or earlier, under which that method *does* work? Meanwhile as you see I do have the absurdly complex 'Win 8 and later' method working! Best wishes, Terry, East Grinstead, UK Without registering and logging into the site you cited it's not possible to see any code, yours or suggested solutions, to understand anything about what you're doing other than you've got some sort of problem with clipboard text. As it happens there is a relatively recent bug (from memory surfaced about 3 years ago) that affects the DataObject method with X64. Various solutions have been suggested which appear to work for some, though probably best to use the clipboard APIs. Peter T Are you quite sure you couldn't read messages? I can do so without logging in. I only need that to contribute or reply. I wouldn't have included a link that wasn't accessible! I just sent the link to my wife on her PC in the next room and confirmed that. Terry, East Grinstead, UK |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a variable's contents onto the clipboard
"Terry Pinnell" wrote in message ... "Peter T" wrote: "Terry Pinnell" wrote in message Hi Garry, Thanks, but isn't that the method I described in the post I referenced from the Excel Forum? As I said there, that fails in Windows 10, delivering nothing when pasted externally. Because of that 'bug' or whatever it is, also referenced in that thread. Are you using Win 7 or earlier, under which that method *does* work? Meanwhile as you see I do have the absurdly complex 'Win 8 and later' method working! Best wishes, Terry, East Grinstead, UK Without registering and logging into the site you cited it's not possible to see any code, yours or suggested solutions, to understand anything about what you're doing other than you've got some sort of problem with clipboard text. As it happens there is a relatively recent bug (from memory surfaced about 3 years ago) that affects the DataObject method with X64. Various solutions have been suggested which appear to work for some, though probably best to use the clipboard APIs. Peter T Are you quite sure you couldn't read messages? I can do so without logging in. I only need that to contribute or reply. I wouldn't have included a link that wasn't accessible! I just sent the link to my wife on her PC in the next room and confirmed that. Terry, East Grinstead, UK The link is accessible and in your OP you said "Hopefully my code extract clearly explains". However all the boxes with code (incl replies) say "Please Login or Register to view this content." Peter T |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a variable's contents onto the clipboard
"Peter T" wrote:
"Terry Pinnell" wrote in message Hi Garry, Thanks, but isn't that the method I described in the post I referenced from the Excel Forum? As I said there, that fails in Windows 10, delivering nothing when pasted externally. Because of that 'bug' or whatever it is, also referenced in that thread. Are you using Win 7 or earlier, under which that method *does* work? Meanwhile as you see I do have the absurdly complex 'Win 8 and later' method working! Best wishes, Terry, East Grinstead, UK Without registering and logging into the site you cited it's not possible to see any code, yours or suggested solutions, to understand anything about what you're doing other than you've got some sort of problem with clipboard text. As it happens there is a relatively recent bug (from memory surfaced about 3 years ago) that affects the DataObject method with X64. Various solutions have been suggested which appear to work for some, though probably best to use the clipboard APIs. Peter T Are you quite sure you couldn't read messages? I can do so without logging in. I only need that to contribute or reply. I wouldn't have included a link that wasn't accessible! I just sent the link to my wife on her PC in the next room and confirmed that. Terry, East Grinstead, UK Ditto Peter's replies! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a variable's contents onto the clipboard
Yes, I've been using this in XP/W7. Haven't tried it on W10 but Peter's
suggestion to use the APIs makes sense given that DataObject is broken in W10! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a variable's contents onto the clipboard
"GS" wrote in message
Yes, I've been using this in XP/W7. Haven't tried it on W10 but Peter's suggestion to use the APIs makes sense given that DataObject is broken in W10! It's not particularly broken in W10 and it's fine in all my systems, incl x64. The bug seems to affect some systems dating back to 2010x64 in W7, further back than I recalled earlier. No excuse as I was involved in one of the early threads about it, though as I couldn't repro it took a while to appreciate there really was a bug. Peter T |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a variable's contents onto the clipboard
On Mon, 13 Jan 2020 15:04:08 +0000, Terry Pinnell
wrote: Are you quite sure you couldn't read messages? I can do so without logging in. I only need that to contribute or reply. I wouldn't have included a link that wasn't accessible! I agree with Peter that the code is not visible. The window that should show the code just says "Please Login or Register to view this content." The rest of the test in the forum is visible. Best regards -- Jesper Kaas - |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a variable's contents onto the clipboard
"Terry Pinnell" wrote in message
OK, understood, I didn't notice that serious snag, sorry! In case either of you or anyone else who end up here is interested, here is my attempt at pasting the entire thread. Sorry but it would take too long to go through all that to figure what you've tried, what's not working, and why not. Try this basic MS example: https://docs.microsoft.com/en-us/off...-the-clipboard This will need to be adapted for use with 64bit Excel, first the API declarations at the top of the form: #If VBA7 Then ' works with all versions of 32 or 64 bit 2010 or later ' All the following APIs are likely to be word wrapped after ' posting, should be on eleven single lines each starting Private Private Declare PtrSafe Function OpenClipboard Lib "User32" (ByVal hWnd As LongPtr) As Long 'Ptr Private Declare PtrSafe Function EmptyClipboard Lib "User32" () As Long Private Declare PtrSafe Function CloseClipboard Lib "User32" () As Long Private Declare PtrSafe Function IsClipboardFormatAvailable Lib "User32" (ByVal wFormat As Long) As Long Private Declare PtrSafe Function GetClipboardData Lib "User32" (ByVal wFormat As Long'Ptr) As LongPtr Private Declare PtrSafe Function SetClipboardData Lib "User32" (ByVal wFormat As Long'Ptr, ByVal hMem As LongPtr) As LongPtr Private Declare PtrSafe Function GlobalAlloc Lib "kernel32.dll" (ByVal wFlags As Long, ByVal dwBytes As LongPtr) As LongPtr Private Declare PtrSafe Function GlobalLock Lib "kernel32.dll" (ByVal hMem As LongPtr) As LongPtr Private Declare PtrSafe Function GlobalUnlock Lib "kernel32.dll" (ByVal hMem As LongPtr) As LongPtr Private Declare PtrSafe Function GlobalSize Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr Private Declare PtrSafe Function lstrcpy Lib "kernel32.dll" Alias "lstrcpyW" (ByVal lpString1 As Any, ByVal lpString2 As Any) As LongPtr #Else ' for use with Excel 2007 or earlier ' the same APIs as in the MS link #End If The declarations in at the top of the two example routines also need to be adaped: Public Sub SetClipboard(sUniText As String) #If VBA7 Then Dim iStrPtr As LongPtr Dim iLock As LongPtr #Else Dim iStrPtr As Long Dim iLock As Long #End If Dim iLen As Long ' rest of the routine same as the MS link Public Function GetClipboard() As String #If VBA7 Then Dim iStrPtr As LongPtr Dim iLock As LongPtr #Else Dim iStrPtr As Long Dim iLock As Long #End If Dim iLen As Long Dim sUniText As String ' rest of the routine same as the MS link If this doesn't do what you want explain why not, with any error messages as applicable. Peter T |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a variable's contents onto the clipboard
"Peter T" wrote:
"Terry Pinnell" wrote in message OK, understood, I didn't notice that serious snag, sorry! In case either of you or anyone else who end up here is interested, here is my attempt at pasting the entire thread. Sorry but it would take too long to go through all that to figure what you've tried, what's not working, and why not. Try this basic MS example: https://docs.microsoft.com/en-us/off...-the-clipboard This will need to be adapted for use with 64bit Excel, first the API declarations at the top of the form: #If VBA7 Then ' works with all versions of 32 or 64 bit 2010 or later ' All the following APIs are likely to be word wrapped after ' posting, should be on eleven single lines each starting Private Private Declare PtrSafe Function OpenClipboard Lib "User32" (ByVal hWnd As LongPtr) As Long 'Ptr Private Declare PtrSafe Function EmptyClipboard Lib "User32" () As Long Private Declare PtrSafe Function CloseClipboard Lib "User32" () As Long Private Declare PtrSafe Function IsClipboardFormatAvailable Lib "User32" (ByVal wFormat As Long) As Long Private Declare PtrSafe Function GetClipboardData Lib "User32" (ByVal wFormat As Long'Ptr) As LongPtr Private Declare PtrSafe Function SetClipboardData Lib "User32" (ByVal wFormat As Long'Ptr, ByVal hMem As LongPtr) As LongPtr Private Declare PtrSafe Function GlobalAlloc Lib "kernel32.dll" (ByVal wFlags As Long, ByVal dwBytes As LongPtr) As LongPtr Private Declare PtrSafe Function GlobalLock Lib "kernel32.dll" (ByVal hMem As LongPtr) As LongPtr Private Declare PtrSafe Function GlobalUnlock Lib "kernel32.dll" (ByVal hMem As LongPtr) As LongPtr Private Declare PtrSafe Function GlobalSize Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr Private Declare PtrSafe Function lstrcpy Lib "kernel32.dll" Alias "lstrcpyW" (ByVal lpString1 As Any, ByVal lpString2 As Any) As LongPtr #Else ' for use with Excel 2007 or earlier ' the same APIs as in the MS link #End If The declarations in at the top of the two example routines also need to be adaped: Public Sub SetClipboard(sUniText As String) #If VBA7 Then Dim iStrPtr As LongPtr Dim iLock As LongPtr #Else Dim iStrPtr As Long Dim iLock As Long #End If Dim iLen As Long ' rest of the routine same as the MS link Public Function GetClipboard() As String #If VBA7 Then Dim iStrPtr As LongPtr Dim iLock As LongPtr #Else Dim iStrPtr As Long Dim iLock As Long #End If Dim iLen As Long Dim sUniText As String ' rest of the routine same as the MS link If this doesn't do what you want explain why not, with any error messages as applicable. Peter T Thanks, but as mentioned in my later posts in that thread I now have it working. With code that at a glance looks very similar to yours above. My original question was seeking expert advice/insights/confirmation on points such as "...seems astonishingly complex." "...seems rather inflexible too." and particularly any better/simpler methods Terry, East Grinstead, UK |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a variable's contents onto the clipboard
"GS" wrote in message
Yes, I've been using this in XP/W7. Haven't tried it on W10 but Peter's suggestion to use the APIs makes sense given that DataObject is broken in W10! It's not particularly broken in W10 and it's fine in all my systems, incl x64. The bug seems to affect some systems dating back to 2010x64 in W7, further back than I recalled earlier. No excuse as I was involved in one of the early threads about it, though as I couldn't repro it took a while to appreciate there really was a bug. Peter T And, as we both know, it's always more reliable to work directly with APIs given the nuances of VBA6/7 and the latter's PtrSafe requirements. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a variable's contents onto the clipboard
Much better tutorial than I have, so it's "out with the old, in with the new"
for me, replacing my existing .bas! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a variable's contents onto the clipboard
"Terry Pinnell" wrote in message
Thanks, but as mentioned in my later posts in that thread I now have it working. With code that at a glance looks very similar to yours above. My original question was seeking expert advice/insights/confirmation on points such as "...seems astonishingly complex." "...seems rather inflexible too." and particularly any better/simpler methods Terry, East Grinstead, UK API approaches can seem complex particularly if new to using APIs, and this example perhaps more than most because different things are going on as part of the whole operation. I guess your choice is just use it as offered or if interested research how APIs work in general and these in particular. However not sure why you describe it as inflexible. Quite the reverse, it's far more flexible than say using the DataObject which is limited to text only. With the APIs you can work with anything the clipboard can accept, and retrieve information about the 'type' of data that's in the clipboard before deciding to 'get' it. That said you would need to adapt the examples for use with other data types. As for any better methods - not really / simpler - indeed use the DataObject! Peter T |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a variable's contents onto the clipboard
"GS" wrote in message ... "GS" wrote in message Yes, I've been using this in XP/W7. Haven't tried it on W10 but Peter's suggestion to use the APIs makes sense given that DataObject is broken in W10! It's not particularly broken in W10 and it's fine in all my systems, incl x64. The bug seems to affect some systems dating back to 2010x64 in W7, further back than I recalled earlier. No excuse as I was involved in one of the early threads about it, though as I couldn't repro it took a while to appreciate there really was a bug. Peter T And, as we both know, it's always more reliable to work directly with APIs given the nuances of VBA6/7 and the latter's PtrSafe requirements. VBA7 doesn't actualy 'require' PtrSafe APIs, merely it can compile and use them even in 32bit. It's Win64 that requires PtrSafe APIs and LongPtr (in effect LongLong in x64) if/as necessary. Sorry if this sounds like semantics...;) Peter T |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a variable's contents onto the clipboard
"GS" wrote in message ...
"GS" wrote in message Yes, I've been using this in XP/W7. Haven't tried it on W10 but Peter's suggestion to use the APIs makes sense given that DataObject is broken in W10! It's not particularly broken in W10 and it's fine in all my systems, incl x64. The bug seems to affect some systems dating back to 2010x64 in W7, further back than I recalled earlier. No excuse as I was involved in one of the early threads about it, though as I couldn't repro it took a while to appreciate there really was a bug. Peter T And, as we both know, it's always more reliable to work directly with APIs given the nuances of VBA6/7 and the latter's PtrSafe requirements. VBA7 doesn't actualy 'require' PtrSafe APIs, merely it can compile and use them even in 32bit. It's Win64 that requires PtrSafe APIs and LongPtr (in effect LongLong in x64) if/as necessary. Sorry if this sounds like semantics...;) Peter T I already know this but thanks for clarifying for other readers, though! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy contents of variable into clipboard | Excel Programming | |||
The contents of the clipboard | Excel Programming | |||
Keep contents in clipboard | Excel Programming | |||
Set variable to clipboard contents? | Excel Programming | |||
Clipboard contents | Excel Programming |