none
Insert Excel Worksheet into Word through VB.Net RRS feed

  • Question

  • I need to programatically create an Excel worksheet and insert it into a Word document. I know that I can create a table within a Word document programatically, but I think I can make it pretty easier by creating it in Excel and then inserting it into the proper place in the Word document using bookmarks.

    Thank you,

    Susan

    Thursday, July 6, 2017 6:01 PM

All replies

  • Hi Susan,

    What do you mean inserting excel worksheet into Word? Do you want to add an embedded object in the word? It seems that you want to create a table in worksheet and then insert the worksheet into the word document, right?

    I think you could use Shapes.AddOLEObject Method to add an worksheet to word document. Here is  the example to add the worksheet to the first bookmark in a word document.

                
       'need add reference Microsoft Office Interop Excel.dll
    
            Dim xlApp As New Excel.Application
    
            Dim wb As Excel.Workbook
    
            Dim ws As Excel.Worksheet
    
            Dim path As String
    
            wb = xlApp.Workbooks.Add()
    
            ws = wb.Sheets(1)
    
            For i = 1 To ws.Range("A1:E5").Cells.Count
    
                ws.Range("A1:E5").Cells(i) = i
    
            Next
    
            path = "C:\Users\Desktop\Blank1.xlsx"
    
            wb.SaveAs(Filename:=path)
    
            doc.Shapes.AddOLEObject(FileName:=path, Anchor:=doc.Bookmarks(1).Range)

    Path is the excel file full name. So you could create a worksheet and save it to an path and use the path to add it to the word document.

    Best Regards,

    Terry

    Friday, July 7, 2017 6:39 AM
  • I agree with Terry. You can insert the worksheet to Word file as an OLE object. Here is just another solution using Spire.Office.

    Imports Spire.Doc
    Imports Spire.Doc.Documents
    Imports System.Drawing
    Imports Spire.Doc.Fields
    Imports Spire.Xls
    
    Namespace InsertExcelToBookmark
    	Class Program
    		Private Shared Sub Main(args As String())
    			Dim wordFile As String = "C:\Users\Administrator\Desktop\Word_template.docx"
    			Dim excelFile As String = "C:\Users\Administrator\Desktop\Excel.xlsx‪"
    
    			'load word file to document
    			Dim document As New Document()
    			document.LoadFromFile(wordFile)
    
    			'get the bookmark where you want to insert worksheet
    			Dim bn As New BookmarksNavigator(document)
    			bn.MoveToBookmark("bookmark2", True, True)
    
    			'create ole picture from excel    
    			Dim olePicture As New DocPicture(document)
    			Dim image As Image = GetExcelImage(excelFile)
    			olePicture.LoadImage(image)
    
    			'insert worksheet to bookmark as ole object
    			Dim temSection As Section = document.AddSection()
    			Dim paragraph As Paragraph = temSection.AddParagraph()
    			paragraph.AppendOleObject(excelFile, olePicture, Spire.Doc.Documents.OleObjectType.ExcelWorksheet)
    			bn.InsertParagraph(paragraph)
    			document.Sections.Remove(temSection)
    
    			'save the file
    			document.SaveToFile("output.docx", Spire.Doc.FileFormat.Docx)
    		End Sub
    		Private Shared Function GetExcelImage(ExcelFile As [String]) As Image
    			'load Excel file
    			Dim workbook As New Workbook()
    			workbook.LoadFromFile(ExcelFile, ExcelVersion.Version2013)
    			Dim sheet As Worksheet = workbook.Worksheets(0)
    			sheet.PageSetup.TopMargin = 0
    			sheet.PageSetup.BottomMargin = 0
    			sheet.PageSetup.LeftMargin = 0
    			sheet.PageSetup.RightMargin = 0
    
    			'show borders        
    			sheet.AllocatedRange.Borders.LineStyle = LineStyleType.Thin
    			sheet.AllocatedRange.Borders(BordersLineType.DiagonalDown).LineStyle = LineStyleType.None
    			sheet.AllocatedRange.Borders(BordersLineType.DiagonalUp).LineStyle = LineStyleType.None
    
    			'save to image
    			Dim lastRow As Integer = sheet.LastRow
    			Dim lastColumn As Integer = sheet.LastColumn
    			Return workbook.Worksheets(0).ToImage(1, 1, lastRow, lastColumn)
    		End Function
    	End Class
    End Namespace




    Friday, July 7, 2017 7:05 AM
  • This did work though I ended up doing something slightly different. I ended up "cutting" the table from Excel and "pasting" it where I had placed a bookmark in the Word document. It was a little cleaner looking since this is a form that will be sent out externally.

    Thank you,

    Susan

    Friday, July 7, 2017 6:28 PM
  • Before .Net here was COM. Before COM there was OLE. OLE is the acronym for Object Linking/Embedding. The difference between linking and embedding is that when linking, the object exists outside of the object it is linked from and for embedding the object exists in the object embedding it.



    Sam Hobbs
    SimpleSamples.Info

    Friday, July 7, 2017 7:09 PM
  • Hi Susan,

    >>I think I can make it pretty easier by creating it in Excel and then inserting it into the proper place in the Word document using bookmarks.

    Could you share us the reason you think it is easier than creating a table in Word document? In my option, creating a table directly in Word is much easier for that you did not need to interop with Excel, and copy and paste, just create a table in the bookmark location.

    To achieve the same result of Terry and Scott4DotNet, below code would be much simple, and I suggest you try to convert it to VB.NET.

    Sub Macro14()
    '
    ' Macro14 Macro
    '
    '
        ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=2, NumColumns:= _
            4, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
            wdAutoFitFixed
        With Selection.Tables(1)
            If .Style <> "Table Grid" Then
                .Style = "Table Grid"
            End If
            .ApplyStyleHeadingRows = True
            .ApplyStyleLastRow = False
            .ApplyStyleFirstColumn = True
            .ApplyStyleLastColumn = False
            .ApplyStyleRowBands = True
            .ApplyStyleColumnBands = False
        End With
    End Sub

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 13, 2017 8:39 AM
  • Creating a table is easy to do within Word, but a lot of the formatting options were not easy to duplicate.

    Thank you,

    Susan

     
    Thursday, July 13, 2017 1:19 PM
  • Hi Susan,

    You could copy the table in excel and use Range.PasteExcelTable to paste it as a table in word.

    Here is the example.

            'need add reference Microsoft Office Interop Excel.dll

            Dim xlApp As New Excel.Application

            Dim wb As Excel.Workbook

            Dim ws As Excel.Worksheet

            wb = xlApp.Workbooks.Add()

            ws = wb.Sheets(1)

            For i = 1 To ws.Range("A1:E5").Cells.Count

                ws.Range("A1:E5").Cells(i) = i

            Next

            ws.Range("A1:E5").Copy()

            doc.Bookmarks(1).Range.PasteExcelTable(False, True, False)

    Best Regards,

    Terry

    Monday, July 17, 2017 8:56 AM