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

S

ship

Hi

This is driving me ABSOLUTELY NUTS!

How can I keep the rows & columns of data that I am copying and pasting
off a website (my own in this case!), into a spreadsheet... WITHOUT
taking all the data formatting?

If I paste out of Ms IE v6 into Ms Excel (2003), it does at least keep
the
columns (something that doesnt happen if I paste out of FireFox, fwiw).

But it pastes with all the formatting & URLs etc - which I DONT WANT!

OK, I can save as .CSV, close, 2 warnings, and re-open but when done
REPEATEDLY this is a damned nuicance!

Any suggestions?


Ship
Shiperton Henethe
 
D

dingbat

ship said:
How can I keep the rows & columns of data that I am copying and pasting
off a website (my own in this case!), into a spreadsheet... WITHOUT
taking all the data formatting?
Click to expand...

Try the "Edit | Paste Special" menu, not pasting with a keystroke
 
D

Dave Peterson

Paste into a helper worksheet that will preserve the formatting and the columns.

Then copy from there, but paste special|values to its rightful home.
 
D

Dave Peterson

You have another suggestion at your other thread.
Hi

This is driving me ABSOLUTELY NUTS!

How can I keep the rows & columns of data that I am copying and pasting
off a website (my own in this case!), into a spreadsheet... WITHOUT
taking all the data formatting?

If I paste out of Ms IE v6 into Ms Excel (2003), it does at least keep
the
columns (something that doesnt happen if I paste out of FireFox, fwiw).

But it pastes with all the formatting & URLs etc - which I DONT WANT!

OK, I can save as .CSV, close, 2 warnings, and re-open but when done
REPEATEDLY this is a damned nuicance!

Any suggestions?

Ship
Shiperton Henethe
Click to expand...
 
S

ship

Dave

I dont follow your drift!
A) What is a "helper worksheet"
B) Where is your thread - I dont seem to be able to see it for some
reason...

Ship
Shiperton Henethe
 
D

Dave Peterson

Insert a new worksheet (just temporary)
Paste there
copy from that new worksheet
go to the real worksheet
paste special|values
delete that temporary worksheet

I didn't notice that you cross posted. That's what my oops was for.
Dave

I dont follow your drift!
A) What is a "helper worksheet"
B) Where is your thread - I dont seem to be able to see it for some
reason...

Ship
Shiperton Henethe
Click to expand...
 
S

ship

Ah Yes that's quite clever - why did I think of that - I'll try it.
(Though why the heck Micro$oft couldnt give us better cleaner
pasting options is a good point - just the sort of thing that
generates MASSIVE bad will against them...!)
Thanks.

Meanwhile I now have the reverse problem
I need to paste OUT of excel and into Dreamweaver without
any formatting, table column widths etc...

I have to do this operation quite a lot and for some time
now I've been saving as a CSV file and then
reopening - but there must a better way!

Maybe some web-friendly plug-in or something, no?

Any thoughts?


Ship
Shiperton Henethe
 
S

ship

P.S. Ah sorry yes one problem is that I need the currency
formatting to stay put! e.g. "£1,000.50" needs to stay visible
in the HTML exactly like that and not get turned into
"1000.5" !

Ship
Shiperton Henethe
 
R

Rajah

I use a free utility, NoteTab light. I copy from Excel and paste into
NoteTab light. Then I have it convert the ^t (tabs) to </td><td>.
Finally, I add the new row info in between the lines (</tr><tr>).

Not bad for 10 rows, but I'm eager to hear what others use to do this
operation more efficiently.

BTW, NoteTab can take the markup and data (from View Source), and from
the Modify -> Strip HTML tags, do just that.

-Rajah
 
D

Dave Peterson

No extra thoughts from me.
Ah Yes that's quite clever - why did I think of that - I'll try it.
(Though why the heck Micro$oft couldnt give us better cleaner
pasting options is a good point - just the sort of thing that
generates MASSIVE bad will against them...!)
Thanks.

Meanwhile I now have the reverse problem
I need to paste OUT of excel and into Dreamweaver without
any formatting, table column widths etc...

I have to do this operation quite a lot and for some time
now I've been saving as a CSV file and then
reopening - but there must a better way!

Maybe some web-friendly plug-in or something, no?

Any thoughts?

Ship
Shiperton Henethe
Click to expand...
 
D

Dave Peterson

I think I'd apply the formatting after the pasting. I'm not sure how you can do
a single paste and sometimes copy formats and sometimes not.
 
S

ship

Sounds like far too many clicks!

I think there used to be some microsoft plug in for
Offfice 2000 (??) but not sure about 2003 which I am now using.
(And Dreamweaver MX 2004, fwiw)


Ship
Shiperton Henethe
 
S

SysMod

This removes formatting so it won't help your currency need, but
anyway:

Sub PasteHtml()
'
' Keyboard Shortcut: Ctrl+Q
' PasteSpecial(Format, Link, DisplayAsIcon, IconFileName, IconIndex,
IconLabel, NoHTMLFormatting)
ActiveSheet.PasteSpecial NoHTMLFormatting:=True
End Sub
 
S

ship

Like you say it wont help the currency requirement
but might be helpful on occassion.

But please can you tell me how & where exactly I need insert
this bit of code. Is it an ms Excel macro? If so how do I get
it inserted & working whenever I open msExcel....?

Ship
Shiperton Henethe
 
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
 

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

How to Import (tab-delimited) .CSV files into msExcel in a single step? 8
How do I trash HTML links en-masse from a spreadsheet? (msExcel 2002) 4
How can I paste a table of data into Excel from a MSIE / web browser? 4
How do I import raw CSV data into an HTML Table ? (i.e. without the f***ing Microsoft Excel formatt 4
How do I paste HTML source code into an email (Outlook 2002) 1
How can I find out which folder an email is in? 2
HOw can I paste data into a column with autofilter? 1
HOW CAN I SCAN DATA WITH COLUMNS AND ROWS DIRECTLY INTO SPREADSHE. 2

Top