How to copy a formatted cell in Excel to a table cell in Word using .NET?

Question

I'm attempting to copy cells, one at a time, from an Excel 2003 (or 2007) spreadsheet to a Word 2003 (or 2007) table. I'd like the code to be version-agnostic, and so am using late binding. The formatting of the contents of the Excel cell, such as color, underline, strike-through, needs to be preserved. My approach is to use a Word doc as a template. It has a table at the top which I can copy to the end of the doc, add rows as needed, and fill in the word table cells with the data from the excel spreadsheet. Unfortunately, all the formatting disappears. All I get is the text itself.

Answer 1

You might have to do the text first and then copy the style manually. You can use Workbook.Styles to get the collection of Microsoft.Office.Interop.Excel.Style and should then be able to create Microsoft.Office.Interop.Word.Styles using that data.

Answer 2

Figured it out. Turns out to be easier than expected.

    Dim appExcel As Excel.Application
    Dim thisWorkbook As Excel.Workbook
    Dim thisWorksheet As Excel.Worksheet

    appExcel = CType(CreateObject("Excel.Application"), Excel.Application)
    thisWorkbook = appExcel.Workbooks.Open("Excelfilename.xls")
    thisWorksheet = CType(thisWorkbook.ActiveSheet, Excel.Worksheet)

    Dim appWord As Word.Application
    Dim thisDoc As Word.Document
    Dim thisWordTable As Word.Table

    ' Use a template word doc that has a table in it.

    appWord = CType(CreateObject("Word.Application"), Word.Application)
    thisDoc = appWord.Documents.OpenNoRepairDialog("templateWordFileName.doc")
    thisDoc.SaveAs("outputDocFileName.doc")

    ' Get a reference to the table.
    thisWordTable = thisDoc.Tables(0)

    ' Copy data from excel to this table.
    CType(thisWorksheet.Cells(5, 1), Excel.Range).Copy()
    thisWordTable.Cell(1, 2).Select()
    appWord.Selection.Paste()

    thisDoc.Save()
    thisDoc.Close()
    appWord.Quit()

    thisWorkbook.Close(False)   ' Don't save any changes to workbook.
    appExcel.Quit()