Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pasting Word table cell with paragraph markers into single Excel c | Excel Discussion (Misc queries) | |||
lose formatting (border) in excel cell after pasting from word | Excel Discussion (Misc queries) | |||
Pasted text from Excel to Word replaces previous paragraph | Excel Programming | |||
Formating, paragraph marks, boxes etc, how do I get rid of them? | Excel Discussion (Misc queries) | |||
Can not see all of a pasted paragraph from Word in a cell. | Excel Discussion (Misc queries) |