OpenText Method Question

[Tony Sinclair]

I am trying to automate the pasting of data into my worksheet. I am
manually copying a table from a website (highlight and Ctrl-C), and
then doing a Paste Special as text, and then using the Import Text
wizard to specify columns and delimiters.

In bumbling through the VBA docs, I found the Workbooks.OpenText
Method, which seems to duplicate the function of the Import Text
wizard. But there are two problems with it ---

1) It wants a filename, but my data will be in the clipboard
2) It wants to put the parsed data into a new workbook with a single
sheet, while I want my data appended to an existing sheet.

Basically I want to be able to highlight a cell and enter a macro key,
and have the table pasted into my worksheet, with the selected cell as
the upper left corner of the table. I don't think it would be worth
my time to figure out how to use the OpenText method as described in
the docs, if I then have to go back and copy all the individual
workbooks into my sheet.

I am a novice at Excel and VBA, but I'm proficient at C#, so I think I
can probably follow an explanation involving objects and methods. Is
there a workaround for the two problems above? Thank you.

 

[Guest]

OpenText method help says very clearly:
Loads and parses a text file as a """""new workbook""""" with a single sheet
that contains the parsed text-file data.

 

[Tony Sinclair]

OpenText method help says very clearly:
Loads and parses a text file as a """""new workbook""""" with a single sheet
that contains the parsed text-file data.

Thank you for your response. I am not trying to argue with the docs,
I am trying to find out if there is a similar method that operates at
the worksheet/cell level (I can't look it up if I don't know what it's
called).

If the OpenText method is the *only* way to automate the Input Text
wizard, then my questions would be, does VBA have some special
filename for the clipboard that I can use in the call to OpenText, and
if so, is there a way to automatically copy the output of OpenText
from the new workbook to my current sheet, and then delete the new
workbook. Thank you.

 

[Dave Peterson]

After you paste into excel, turn on the macro recorder.

Then select the range you just pasted
data|text to columns
And follow that wizard.

Stop the recorder and look at the code.

=======
Then as a second assignment, start a new (test) workbook.

Data|Import External data|New Web Query
(xl2003's wording)
and follow that wizard.

And see if you like that.

 

[Tony Sinclair]

After you paste into excel, turn on the macro recorder.

Then select the range you just pasted
data|text to columns
And follow that wizard.

Stop the recorder and look at the code.

Thank you, this looks promising. There are some problems I'll have to
tinker with:
If I do a simple paste, the whole table is pasted as one row, so that
doesn't work.
If I do a paste special as text, there is more than one column, and
the Text to Columns wants just one column at a time.
If I do a Text to Columns on an interior column, it doesn't move the
following columns over like the Import Text wizard does; it overwrites
them.

Still, it looks like if I play with it for a while, I can figure a way
to make it work --- working from right to left, and moving columns
over to create the space I need as I go.

=======
Then as a second assignment, start a new (test) workbook.

Data|Import External data|New Web Query
(xl2003's wording)
and follow that wizard.

And see if you like that.

I could not get this to work. In Excel 2K7, I assume that the
equivalent command is Data|Get External Data|From Web, and it pops up
a form for me to enter the URL. The selection from the web page seems
to work, but what the result is always an error message - probably
because the table is generated by a script on the page, so the URL
isn't enough.

I'll play with the first suggestion some more tonight. Thanks again.

 

[Dave Peterson]

If worse comes to worst, you may want to paste into NotePad. Then save that
file as a .txt file.

And use file|open???

(or Opentext <bg>)