OpenText Method Question
 

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.

OpenText Method Question
 

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" wrote:

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.

OpenText Method Question
 

On Thu, 29 Mar 2007 17:42:01 -0700, Joel
wrote:
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.




"Tony Sinclair" wrote:

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.

OpenText Method Question
 

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 wrote:

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.

--

Dave Peterson

OpenText Method Question
 

On Thu, 29 Mar 2007 20:29:58 -0500, Dave Peterson
wrote:

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.

OpenText Method Question
 

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)

Tony Sinclair wrote:

On Thu, 29 Mar 2007 20:29:58 -0500, Dave Peterson
wrote:

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