Enter Text From Excel cells to Word and retain the formatting

Question

I have the fllowing code

strMyFormat1 = oWB.Worksheets(strSelectedSheet).Range(strStemLocation).NumberFormat

WordApp.Selection.TypeText Format(oWB.Worksheets(strSelectedSheet).Range(strStemLocation), strMyFormat1) & vbCr

But it does not retain the format of the Excel Cell

e.g. if the Excel Cell is bold I want that to be retained when entering into my word doc

I know it works with copy and paste but I dont want to do that

Answer 1

There are many parts to cell formatting, but here are a few to get you started:

Dim rng As Excel.Range
Set rng = oWB.Worksheets(strSelectedSheet).Range(strStemLocation)
With Selection
    .Text = rng.Text
    .Font.Bold = rng.Font.Bold
    .Font.Color = rng.Font.Color
End With

Note: use the Text property of the range object to get the formatted version; use Value to get the unformatted version. Also note that the range object (rng) should be a single cell.

Answer 2

This sample procedure creates a new Word document and adds the contents of each cell in the active Excel worksheet, keeping (some of) the formatting:

Sub ExportToWord_Example2()
    Dim WordApp As Word.Application
    Dim doc As Word.Document
    Dim rng As Range

    Set WordApp = CreateObject("Word.Application")
    With WordApp
        .Visible = True
        Set doc = .Documents.Add
    End With
    For Each rng In ActiveSheet.UsedRange
        With doc.Paragraphs(doc.Paragraphs.Count).Range
            .Text = rng.Text
            .Font.Bold = rng.Font.Bold
            .Font.Color = rng.Font.Color
        End With
        doc.Range.InsertParagraphAfter
    Next rng
End Sub

Answer 3

So you have a cell and you want to copy its contents into a word Doc?
I'd do:

Cells(currentRow, currentCol).Copy
.Cell(2,1).Range.PasteAndFormat (wdFormatOriginalFormatting)

This will copy the text EXACTLY as it was originally.. font, bold, color etc.

Reminder:
Cell = the cell in the Excel spreadsheet
.Cell = the cell you want to paste into in the Word document

But say you want to modify the font to Arial to better match the rest of your WordDoc but still leave the original colors and bold etc IF any exists.. just add:

.Cell(2,1)Range.Font.Name ("Arial")

You can do the same for any of the other Font attributes like size etc The key is making these changes AFTER it's copied into the Word doc.

Hope this helps someone out. -S