How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the fo

M

mail3

It might be easier to record a macro and then change it.

Tools > Macro > Record New Macro
Name PasteHTML
Shortcut Ctrl+Shift+V (or some other key you don't already use)
Store Macro in: Personal Macro Workbook
Click OK

Switch to a web page and copy some text
Switch back to Excel
Edit > Paste Special > Text
Click Stop Recording on the macro toolbar

Window > Unhide > Personal.xls

Tools > macros > macros
Select PasteHTML, click Edit
change the line to read

ActiveSheet.PasteSpecial NoHTMLFormatting:=True

File > Close and return to Excel
Window > Hide
File > Exit and save changes to Personal.xls

I'd also recommend a good book on learning VBA such as John
Walkenbach's, see
http://www.sysmod.com/spreads.htm#Books

Good luck,

Patrick
 
S

ship

Hi

Nice try - except that you slightly miss the point.
The problem is that as things stand it has to be
Pasted "AS HTML" (i.e. not "as text") otherwise
msExcel loses the table structure!

And of course if you paste a large table "as HTML" into
a spreadsheet it takes all the stupid formatting including
all the URLs etc... and this will at best massively
slow up msExcel - but at worse actually crash it!

Conclusion
Yes may be one could some macro way of doing
it involving saving as .CSV closing and reopening
but that strikes me as EXTREMELY MESSY...!

Ship
Shiperton Henethe
 
S

ship

P.S.
Plus it often goes wrong!
When you paste HTML into a spread sheet you sometimes
get a table cell throwing a carriage return, with the result
that the data in excel ends up on TWO (rather than just one)
lines.

Ship
Shiperton Henethe
 
S

ship

P.S.
Plus it often goes wrong!
When you paste HTML into a spread sheet you sometimes
get a table cell throwing a carriage return, with the result
that the data in excel ends up on TWO (rather than just one)
lines.

Ship
Shiperton Henethe
 
S

ship

P.S.
Plus it often goes wrong!
When you paste HTML into a spread sheet you sometimes
get a table cell throwing a carriage return, with the result
that the data in excel ends up on TWO (rather than just one)
lines.

Ship
Shiperton Henethe
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Is there any *quick* way to view data records *vertically* instead of as horizontal rows? 3
How can I create an HTML email in Outlook that shows images at thecorrect size? 1
How can I paste WITHOUT cell references changing (and without usingabsolute cell addresses) [EXCEL20 8
How can I move data from a row range to a column range 4
How do I trash HTML links en-masse from a spreadsheet? (msExcel 2002) 4
How to Import (tab-delimited) .CSV files into msExcel in a single step? 8
How can I combine 2 columns of data into 1 and format it as follow 1
How do I paste HTML source code into an email (Outlook 2002) 1

Top