Hello. I am trying to put data I copy from a bank into an Excel 2007
worksheet using VBA. I don't know in advance the size or 'shape' of the data
but I do know that it is presented as a "table" of rows and columns.

All I want to do is paste the 'table' into a range on a sheet as text. I
can do a manual paste to the first cell of the range and the data populates
the range of cells perfectly. However, when I use VBA to paste the data to
the range (and to a text string), I get various errors depending on how I
attempt to do the paste. For
example:

....
dim myDataObj as new DataObject
dim aRange as range
set aRange = ...
aRange.Activate
myDataObj.GetFromClipboard
aRange = myDataObj.GetText OR aRange = myDataObject.GetText(1)

(1) In either case, I get the error message "Invalid FORMATETC structure."

I've tried replacing the last line with different paste operations that
yielded the following results:


(2) "aRange.PasteSpecial Paste:=xlValues": I get the the message "Method of
Range class failed."

(3) "aRange = myDataObject.GetFromClipboard:": I get the message "Expected
function or variable." Interestingly enough, if I add an argument to
GetFromClipboard method--ie "aRange = myDataObject.GetFromClipboard(0), I get
a different error message, "Wrong number of arguments or invalid property
assignment" (Does this mean that with proper syntax, the operation could
succeed? And what IS the syntax?)

(4) "SENDKEYS ("^v"), wait:=True", with the upper left cell of the range
having been activated: no error message is generated but no data gets pasted
into the sheet either.

(5) I even tried to use GETFORMAT prior to setting the arg to GETTEXT in the
original attempt. GETTEXT tells me that the clipboard contains data in 4
formats, but nothing different happens if I use any one of them: I still get
the same "Invalid FORMATETC structure" message.


What am I doing wrong? Again, a manual paste works perfectly. I am sure the
answer is staring me in the face but I just
don't see it.

Thanks in advance for any perspective or solution anyone is able to provide.