Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old January 13th 20, 06:36 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 36
Default 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   Report Post  
Old January 13th 20, 07:26 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 160
Default 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   Report Post  
Old January 13th 20, 11:25 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,112
Default 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   Report Post  
Old January 13th 20, 11:29 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,112
Default 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   Report Post  
Old January 14th 20, 12:01 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 36
Default 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   Report Post  
Old January 14th 20, 12:11 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 36
Default 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   Report Post  
Old January 14th 20, 12:34 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,112
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy contents of variable into clipboard Simka Excel Programming 5 October 29th 08 05:35 PM
The contents of the clipboard [email protected] Excel Programming 1 October 3rd 06 11:29 AM
Keep contents in clipboard Kenny Excel Programming 0 September 20th 05 08:45 PM
Set variable to clipboard contents? Fred Smith Excel Programming 2 September 5th 04 08:23 PM
Clipboard contents Basil[_2_] Excel Programming 0 October 8th 03 06:05 PM


All times are GMT +1. The time now is 07:24 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017