Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 12th 20, 05:34 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

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   Report Post  
Old January 12th 20, 07:57 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

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   Report Post  
Old January 13th 20, 01:41 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

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   Report Post  
Old January 13th 20, 02:02 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
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   Report Post  
Old January 13th 20, 04:04 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
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   Report Post  
Old January 13th 20, 04:29 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
...
"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   Report Post  
Old January 13th 20, 04:40 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

"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   Report Post  
Old January 13th 20, 04:43 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

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   Report Post  
Old January 13th 20, 05:07 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

"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   Report Post  
Old January 13th 20, 05:50 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2010
Posts: 6
Default 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 -


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 06:39 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