the first question I have is "Can you manually get the data if you performed
the same operations using the mouse? if yo can't do it manually then yuo
won't be able to do it from VBA. If you can do it manually can you explain
the steps you use to get the data.

There are lot of methods to import data into excel. Warning, if it is a PDF
data yhou probably won't be successful.

Her are some questions I would like answered.

1) What type of file is the source?
2) If it is a webpage can you import the data using the menu Data - Import
External Data - New Web query.3) Is the file a Database. Can you import the
data using Data - Import External Data - New Database query?
3) Is the file a text file? try reading the file wiht Notepad and see if the
data is readable. Post some lines of the file so we can see what it looks
like. You may bew able to import the file using the menu Data - Import
External Data - Import Data.

To get VBA code you may be able to record a macro while performing the above
operation to get code.

"BlackBAR" wrote:

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.