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

ship wrote:

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

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

Ship
Shiperton Henethe

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

--

Dave Peterson

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

No, I haven't.

Maybe Ship will want to.

Dave H wrote:

Dave Peterson Wrote:
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

--

Dave Peterson

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

--
Dave H

--

Dave Peterson

You have another suggestion at your other thread.

ship wrote:

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.

Dave Peterson wrote:

You have another suggestion at your other thread.

ship wrote:

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

--

Dave Peterson

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 wrote:
Oops. Never mind.

Dave Peterson wrote:

You have another suggestion at your other thread.

ship wrote:

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

--

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.

ship wrote:

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 wrote:
Oops. Never mind.

Dave Peterson wrote:

You have another suggestion at your other thread.

ship wrote:

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

--

Dave Peterson

--

Dave Peterson

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

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

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

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

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

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

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

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