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

[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

 

[dingbat]

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?

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

 

[ship]

Doesnt work!
Either you paste HTML, or you lose the columns

Ship
Shiperton Henethe

 

[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.

 

[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

 

[Dave Peterson]

Oops. Never mind.

You have another suggestion at your other thread.

 

[Dave H]

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.

ship wrote:

Doesnt work!
Either you paste HTML, or you lose the columns

Ship
Shiperton Henethe

Have you tried a web query under data/import external data?

 

[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

 

[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

 

[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

 

[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

 

[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

 

[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

 

[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.

 

[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

 

[Dave Peterson]

No, I haven't.

Maybe Ship will want to.

Have you tried a web query under data/import external data?

 

[William Tasso]

Fleeing from the madness of the jungle
Dave Peterson <[email protected]> stumbled into
news:alt.www.webmaster,microsoft.public.excel,microsoft.public.excel.misc
and said:

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.

If this is a repetitive task, I'd suggest a little scripting (VBA seems
appropriate) would tick the boxes nicely.

 

[Dave Peterson]

That seems like a reasonable approach. Maybe the OP will try it.

 

[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

 

[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