I've only used dataobject to copy single values.

How about a different approach?

You tell the user to select the destination cell, then do the rest in code.
Open the sending workbook (in readonly mode) in the same instance, do the
copy|paste and then close the sending workbook.

If this doesn't work for you and you don't get a better answer here, you may
want to ask in a VB group. Maybe some of those users are more familiar with
dataobject????



wrote:

On 31 Jan, 15:39, Dave Peterson wrote:
So I have a question for you.

Are you pasting just the value or do you want to include formatting?

If you're pasting just values, your code could use the windows clipboard.

Option Explicit
Sub testme()

Dim MyDataObj As DataObject
Dim myVal As Variant

Set MyDataObj = New DataObject

myVal = Sheet1.Range("A1").Value
MyDataObj.SetText myVal
MyDataObj.PutInClipboard

End Sub

Thanks Dave, I didn't know about the DataObject, so it was a useful
post for me.
I am happy to get the values only, but my example was simplified: in
the real application, I need to copy a table from the spreadsheet, so
"myVal" would end up being an array of variants. It seems to me that
MyDataObj.SetText can only work on strings. CPearson's website was
also useful, but didn't mention the case of copying anything different
from a string. Is that be possible?

In any case, thanks for your reply. BTW my system is: "Windows XP
Professional" and Excel 2003 SP2
gc

--

Dave Peterson