VBA Copy Special (Values Only)

[cgmojoco]

Hi there -

Copying formatted cells from excel on my local machine and pasting to MS Access on a remote machine via MS remote desktop connection protocol.

I get a message that the data on the Clipboard is damaged.

If I first paste the data into a .txt file on my local machine...and then copy the data again from the local.txt file (cleaning all non-value specific information from the data) and past to remote machine MS access it works fine.

Is there a way to copy only `value` data into memory from excel via VBA that I might just assign to a button?

If so, what is a good place to start VBA wise?

Thank you in advance

Christopher

 

[cgmojoco]

I'm thinking something like this?
This doesn't seem to do the trick though....I must be missing something syntax wise?

Sub CopyValuesToMemory()
Set MyData = New DataObject
MyData.SetText Application.Selection
MyData.PutInClipboard
End Sub

 

[cgmojoco]

Bump

 

[cgmojoco]

Anybody?

 

[cgmojoco]

Nevermind figured it out---also supports arrays (separating by carriage returns):

Sub CopyCellContentValuesToClipboard()
'Copies cell or cells to clipboard without format (values only).  Array elements are separated by carriage return.
Dim MyData As DataObject
Dim s As String
Dim i As Long
Set MyData = New DataObject
If IsArray(Selection.Value) Then
s = Selection.Cells(1).Value
For i = 2 To Selection.Cells.Count
s = s & vbCrLf & Selection.Cells(i).Value
Next i
MyData.SetText s
Else
MyData.SetText Selection.Value
End If
MyData.PutInClipboard
End Sub

 

[cgmojoco]

Is anyone interested in building this out to support more than 1 dimensional arrays?

Right now, it only supports 1*n or n*1 arrays.

(Either by writing in an error message if array size isn't correct, or re-writing to support further dimension arrays).

Thanks in advance

 

[cgmojoco]

And this no longer works....PLEASE HELP!
Apparently Windows 10 doesn't like to copy just values....anybody have an easy solution?
I found this but not sure I understand what to actually do as I cannot get it working:
VBA Copy to Clipboard, Paste and Clear - wellsr.com

 

[cgmojoco]

Ok found another reference that explained things a bit better (at least for me):
How To Use VBA Code To Copy Text To The Clipboard — The Spreadsheet Guru

Now I am stuck trying to figure out how to use the new function to get this to process more than one cell to the clipboard as values only.

See below Sub `CopyCellContent`. It is meant to copy all cells to clipboard as text only using the new function/method that is apparently required in Windows 10 and or new versions of Office...

:

'Handle 64-bit and 32-bit Office
#If VBA7 Then
  Private Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
  Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
  Private Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As LongPtr, _
    ByVal dwBytes As LongPtr) As LongPtr
  Private Declare PtrSafe Function CloseClipboard Lib "user32" () As LongPtr
  Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
  Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As LongPtr
  Private Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
    ByVal lpString2 As Any) As LongPtr
  Private Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As LongPtr, _
    ByVal hMem As LongPtr) As LongPtr
#Else
  Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
  Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
  Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
    ByVal dwBytes As Long) As Long
  Private Declare Function CloseClipboard Lib "user32" () As Long
  Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
  Private Declare Function EmptyClipboard Lib "user32" () As Long
  Private Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
    ByVal lpString2 As Any) As Long
Private Declare Function SetClipboardData Lib "user32" (ByVal wFormat _
    As Long, ByVal hMem As Long) As Long
#End If

Const GHND = &H42
Const CF_TEXT = 1
Const MAXSIZE = 4096

Function ClipBoard_SetData(MyString As String)
'PURPOSE: API function to copy text to clipboard
'SOURCE: www.msdn.microsoft.com/en-us/library/office/ff192913.aspx

Dim hGlobalMemory As Long, lpGlobalMemory As Long
Dim hClipMemory As Long, X As Long

'Allocate moveable global memory
  hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)

'Lock the block to get a far pointer to this memory.
  lpGlobalMemory = GlobalLock(hGlobalMemory)

'Copy the string to this global memory.
  lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)

'Unlock the memory.
  If GlobalUnlock(hGlobalMemory) <> 0 Then
    MsgBox "Could not unlock memory location. Copy aborted."
    GoTo OutOfHere2
  End If

'Open the Clipboard to copy data to.
  If OpenClipboard(0&) = 0 Then
    MsgBox "Could not open the Clipboard. Copy aborted."
    Exit Function
  End If

'Clear the Clipboard.
  X = EmptyClipboard()

'Copy the data to the Clipboard.
  hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)

OutOfHere2:
  If CloseClipboard() = 0 Then
    MsgBox "Could not close Clipboard."
  End If

End Function
Sub CopyCellContent()
Dim s As String
Dim i As Long
Set MyData = New DataObject
If IsArray(Selection.Value) Then
s = Selection.Cells(1).Value
For i = 2 To Selection.Cells.Count
s = s & vbCrLf & Selection.Cells(i).Value
Next i
ClipBoard_SetData s
Else
ClipBoard_SetData Selection.Value
End If
ClipBoard_SetData Selection.Value
End Sub

 

[cgmojoco]

Bump, does anyone know how to solve this?

 

[ArchibaldTutle]

Hello
I don’t know if this is any small help, I am no clipboard expert, but to put a range of values into the clipboard from a spreadsheet range, a simple range copy would usually fill up all the clipboards.
Then maybe you could have a look at what text is on the Windows clipboard. If the text looks OK, then maybe putting it back in the clipboard will somehow cure what ever strange problem is. But I never noticed a problem in the text in it..

Some thing simple like this should tell you what text is in the windows clipboard I think …

Sub WhatsUpClip() '  https://www.mrexcel.com/board/threads/vba-copy-special-values-only.557466/
Range("A1:B2").Copy

Dim MyData As DataObject
 Set MyData = New DataObject
Dim AString As String
 MyData.GetFromClipboard
 Let AString = MyData.GetText

Dim Scunthorpe As Long
    For Scunthorpe = 1 To Len(AString)
     Let Range("D" & Scunthorpe & "").Value = "Chr(" & AscW(Mid(AString, Scunthorpe, 1)) & ")"
    Next Scunthorpe
End Sub

I get typically what expect

Book1.xls
	A	B	C	D	E
1	a	b		Chr(97)
2	c	d		Chr(9)
3				Chr(98)
4				Chr(13)
5				Chr(10)
6				Chr(99)
7				Chr(9)
8				Chr(100)
9				Chr(13)
10				Chr(10)
11
Sheet2

Analysing that output shows that you have what you should

a
vbTab
b
vbCr
vbLf
c
vbTab
d
vbCr
vbLf


So got what I expected in my older operating systems , I don’t favour the newer ones…
(I guess you know that Windows clipboard convention for a cell separator to paste in Excel is the vbTab)
( Of course if you are using Windows 10 your likely to be in trouble at some point since the clipboard is riddle with bugs…
One thing that might help is to make sure you have no browsers open when doing anything with the clipboard. )

Brian