How to copy to cashe?

[Denis Petrov]

Dear VBA gurus

I have data
11111, abc, xxx,
22222, def, yyy
33333, ghi, zzz

I need to create a VBA code to copy vlaue
11111|22222|33333 (yes, that exact format with |) to the cache so user can
Paste, please HELP!!!!

Thanks to all

 

[quartz]

Denis,

One method follows. Copy both the sub and the function below into a standard
code module. This assumes that each line of your data resides in one cell (or
column) - you didn't specify about this...
Anyway, select the cells containing your data first, then run the Sub
TestThis(). After it runs, the result is placed in the clipboard and you can
paste it from there.

Sub TestThis()

Dim rCell As Range
Dim sData As String
For Each rCell In Selection
If rCell.FormulaR1C1 <> "" Then sData = sData & Left(rCell.FormulaR1C1,
5) & "|"
Next
sData = Left(sData, Len(sData) - 1)
Call ClipboardAddString(sData)
MsgBox sData
End Sub

Public Function ClipboardAddString(argString As String)
'REQUIRED: REFERENCE TO MICROSOFT FORMS 2.0 OBJECT LIBRARY
'PROGRAMMATICALLY PLACE DATA IN THE CLIPBOARD;
Dim objData As DataObject
Set objData = New DataObject
objData.SetText argString
objData.PutInClipboard
End Function

HTH.

 

[quartz]

Denis,

Also, I should have pointed out (although it is included in my comments in
the function) that you need to set a reference to "Microsoft Forms 2.0 Object
Library".
Do this by opening the visual basic editor, then goto <Tools> then
<References> then check the appropriate box.

HTH/

 

[Denis Petrov]

qurtz,

this is great!!!!

The only problem is that I can not find MICROSOFT FORMS 2.0 OBJECT LIBRARY
on my Excel 2003. I do have SP1 and installed O2003PIA. Please help!

thanks again for your help. I have pieces of code working, until it come to
DataObject...