Importing Word Paragraph to Excel - 1 cell, same formating -- I lose the format
 

I am trying to copy a formated paragraph in word, with SHIFT Enter. This
text also has BOLD, and underline text in it.

I want to copy this to a single cell -- retaining the same format (i.e. bold
and underline) as well as change the SHIFT ENTER characters to ALT ENTER.

If I do a Copy from Word, after selecting the paragraph, and paste it into
Excel -- all the SHIFT Enter goes to new lines in Excel.

I have tried to first copy this over to Word, change all the SHIFT Enter's
to ALT Enter, and then paste to Excel -- but I either lose the format, or I
get back the multiple lines.

Reading some posts suggested that I convert all the SHFT enter's to $$$, and
then in Excel Replace the $$$ with ALT ENTER. Problem is, I lose the
formating, or everthing goes to plain text.

Is there a way to copy a paragraph to Excel -- retaining the same format?

Thanks

Importing Word Paragraph to Excel - 1 cell, same formating -- I lose the format
 

I doubt it. bolding and underlining just part of a cell contents is not one of
Excel's strong points.

On Mon, 21 Feb 2005 20:08:42 -0600, "Chris DeNardis"
wrote:

I am trying to copy a formated paragraph in word, with SHIFT Enter. This
text also has BOLD, and underline text in it.

I want to copy this to a single cell -- retaining the same format (i.e. bold
and underline) as well as change the SHIFT ENTER characters to ALT ENTER.

If I do a Copy from Word, after selecting the paragraph, and paste it into
Excel -- all the SHIFT Enter goes to new lines in Excel.

I have tried to first copy this over to Word, change all the SHIFT Enter's
to ALT Enter, and then paste to Excel -- but I either lose the format, or I
get back the multiple lines.

Reading some posts suggested that I convert all the SHFT enter's to $$$, and
then in Excel Replace the $$$ with ALT ENTER. Problem is, I lose the
formating, or everthing goes to plain text.

Is there a way to copy a paragraph to Excel -- retaining the same format?

Thanks

Importing Word Paragraph to Excel - 1 cell, same formating -- I lose the format
 

Agreed you may not be able to hold the format during the
paste but you can restore it with code like this:
Sub Macro1()
Range("C4").Select
ActiveCell.FormulaR1C1 = "AAAAAAAAAA"

With ActiveCell.Characters(Start:=4, Length:=4).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 5
End With
End Sub

This turns position 4 to 8 bold and blue

Regards,
Mike
-----Original Message-----
I am trying to copy a formated paragraph in word, with
SHIFT Enter. This
text also has BOLD, and underline text in it.

I want to copy this to a single cell -- retaining the
same format (i.e. bold
and underline) as well as change the SHIFT ENTER
characters to ALT ENTER.

If I do a Copy from Word, after selecting the paragraph,
and paste it into
Excel -- all the SHIFT Enter goes to new lines in Excel.

I have tried to first copy this over to Word, change all
the SHIFT Enter's
to ALT Enter, and then paste to Excel -- but I either
lose the format, or I
get back the multiple lines.

Reading some posts suggested that I convert all the SHFT
enter's to $$$, and
then in Excel Replace the $$$ with ALT ENTER. Problem
is, I lose the
formating, or everthing goes to plain text.

Is there a way to copy a paragraph to Excel -- retaining
the same format?

Thanks


.

Importing Word Paragraph to Excel - 1 cell, same formating -- I lose the format
 

Chris

Here's the best I could do with it. Note that the macro has to pause at the
end for you to paste in Excel, then come back and clear a message box. I
couldn't get it to work otherwise. I don't really expect this to be a
workable solution, I just wanted to see if I could do it.

Sub CopyToExcel()

Dim dTemp As Document
Dim sPath As String

'Copy selection to new document and save as html
sPath = Environ("temp") & "\Dtemp.html"
Selection.Copy

Set dTemp = Documents.Add

dTemp.Range.Paste

On Error Resume Next
Kill sPath & "Dtemp.html"
On Error GoTo 0

dTemp.SaveAs sPath & "Dtemp.html", wdFormatHTML

dTemp.Close

Dim lTextStart As Long, lAnchEnd As Long
Dim lTextEnd As Long
Dim fso As Scripting.FileSystemObject
Dim ts As Scripting.TextStream
Dim sHTML As String

'Read in the html
Set fso = New Scripting.FileSystemObject
Set ts = fso.getfile(sPath & "Dtemp.html").openastextstream(1, -2)

sHTML = ts.readall

lTextStart = InStr(1, sHTML, "<p class=")
lTextStart = InStr(lTextStart, sHTML, "") + 1
lTextEnd = InStr(lTextStart, sHTML, "</p") - 1

Dim sStartH As String
Dim sEndH As String

'Create a stripped down html
sStartH = "<html<stylebr{mso-data-placement:same-cell;}</style" &
_
"<body<table<tr<td"
sEndH = "</td</tr</table</body</html"

ts.Close

Set ts = fso.CreateTextFile(sPath & "Dtemp.html", True, False)

ts.Write sStartH & Mid(sHTML, lTextStart, lTextEnd - lTextStart + 1)
& sEndH

ts.Close

Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook

'Open the new html in Excel and copy the used range
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo 0

If Not xlApp Is Nothing Then
Set xlWb = xlApp.Workbooks.Open(sPath & "Dtemp.html")
xlWb.Sheets(1).UsedRange.Copy
xlWb.Windows(1).Visible = False
MsgBox "Paste In Excel, then click OK"
xlWb.Close False
Set xlApp = Nothing
End If

End Sub

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Chris DeNardis wrote:
I am trying to copy a formated paragraph in word, with SHIFT Enter.
This text also has BOLD, and underline text in it.

I want to copy this to a single cell -- retaining the same format
(i.e. bold and underline) as well as change the SHIFT ENTER
characters to ALT ENTER.

If I do a Copy from Word, after selecting the paragraph, and paste it
into Excel -- all the SHIFT Enter goes to new lines in Excel.

I have tried to first copy this over to Word, change all the SHIFT
Enter's to ALT Enter, and then paste to Excel -- but I either lose
the format, or I get back the multiple lines.

Reading some posts suggested that I convert all the SHFT enter's to
$$$, and then in Excel Replace the $$$ with ALT ENTER. Problem is,
I lose the formating, or everthing goes to plain text.

Is there a way to copy a paragraph to Excel -- retaining the same
format?

Thanks

Importing Word Paragraph to Excel - 1 cell, same formating -- I lose the format
 

This one's better. Run this with the text selected in Word, then when you
go to Excel, choose PasteSpecial - Unicode Text

Sub CopyToExcel()

Dim dTemp As Document
Dim sPath As String

'Copy selection to new document and save as html
sPath = Environ("temp") & "\Dtemp.html"
Selection.Copy

Set dTemp = Documents.Add

dTemp.Range.Paste

On Error Resume Next
Kill sPath & "Dtemp.html"
On Error GoTo 0

dTemp.SaveAs sPath & "Dtemp.html", wdFormatHTML

dTemp.Close

Dim lTextStart As Long, lAnchEnd As Long
Dim lTextEnd As Long
Dim fso As Scripting.FileSystemObject
Dim ts As Scripting.TextStream
Dim sHTML As String

'Read in the html
Set fso = New Scripting.FileSystemObject
Set ts = fso.getfile(sPath & "Dtemp.html").openastextstream(1, -2)

sHTML = ts.readall

lTextStart = InStr(1, sHTML, "<p class=")
lTextStart = InStr(lTextStart, sHTML, "") + 1
lTextEnd = InStr(lTextStart, sHTML, "</p") - 1

Dim sStartH As String
Dim sEndH As String

'Create a stripped down html
sStartH = "<stylebr{mso-data-placement:same-cell;}</style" & _
"<table<tr<td"
sEndH = "</td</tr</table"

ts.Close

Dim oDataObj As DataObject

'Put text into clipboard
Set oDataObj = New DataObject

oDataObj.SetText sStartH & Mid(sHTML, lTextStart, lTextEnd -
lTextStart + 1) & sEndH
oDataObj.PutInClipboard

End Sub

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Dick Kusleika wrote:
Chris

Here's the best I could do with it. Note that the macro has to pause
at the end for you to paste in Excel, then come back and clear a
message box. I couldn't get it to work otherwise. I don't really
expect this to be a workable solution, I just wanted to see if I
could do it.
Sub CopyToExcel()

Dim dTemp As Document
Dim sPath As String

'Copy selection to new document and save as html
sPath = Environ("temp") & "\Dtemp.html"
Selection.Copy

Set dTemp = Documents.Add

dTemp.Range.Paste

On Error Resume Next
Kill sPath & "Dtemp.html"
On Error GoTo 0

dTemp.SaveAs sPath & "Dtemp.html", wdFormatHTML

dTemp.Close

Dim lTextStart As Long, lAnchEnd As Long
Dim lTextEnd As Long
Dim fso As Scripting.FileSystemObject
Dim ts As Scripting.TextStream
Dim sHTML As String

'Read in the html
Set fso = New Scripting.FileSystemObject
Set ts = fso.getfile(sPath & "Dtemp.html").openastextstream(1,
-2)
sHTML = ts.readall

lTextStart = InStr(1, sHTML, "<p class=")
lTextStart = InStr(lTextStart, sHTML, "") + 1
lTextEnd = InStr(lTextStart, sHTML, "</p") - 1

Dim sStartH As String
Dim sEndH As String

'Create a stripped down html
sStartH =
"<html<stylebr{mso-data-placement:same-cell;}</style" & _
"<body<table<tr<td"
sEndH = "</td</tr</table</body</html"

ts.Close

Set ts = fso.CreateTextFile(sPath & "Dtemp.html", True, False)

ts.Write sStartH & Mid(sHTML, lTextStart, lTextEnd -
lTextStart + 1) & sEndH

ts.Close

Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook

'Open the new html in Excel and copy the used range
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo 0

If Not xlApp Is Nothing Then
Set xlWb = xlApp.Workbooks.Open(sPath & "Dtemp.html")
xlWb.Sheets(1).UsedRange.Copy
xlWb.Windows(1).Visible = False
MsgBox "Paste In Excel, then click OK"
xlWb.Close False
Set xlApp = Nothing
End If

End Sub


Chris DeNardis wrote:
I am trying to copy a formated paragraph in word, with SHIFT Enter.
This text also has BOLD, and underline text in it.

I want to copy this to a single cell -- retaining the same format
(i.e. bold and underline) as well as change the SHIFT ENTER
characters to ALT ENTER.

If I do a Copy from Word, after selecting the paragraph, and paste it
into Excel -- all the SHIFT Enter goes to new lines in Excel.

I have tried to first copy this over to Word, change all the SHIFT
Enter's to ALT Enter, and then paste to Excel -- but I either lose
the format, or I get back the multiple lines.

Reading some posts suggested that I convert all the SHFT enter's to
$$$, and then in Excel Replace the $$$ with ALT ENTER. Problem is,
I lose the formating, or everthing goes to plain text.

Is there a way to copy a paragraph to Excel -- retaining the same
format?

Thanks