Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
opentext method | Excel Programming | |||
OpenText Method | Excel Programming | |||
OpenText Method failure | Excel Programming | |||
OpenText Method of Workbook Object Question | Excel Programming | |||
How to use Opentext method with xlFixedWidth? | Excel Programming |