none
How can I export the table to my word application? RRS feed

  • Question

  • Hello all ,

                 I need some help in my task .

                 In my application , I created a table in my excel document. How can I export it to a word document using ms ole function? I can create table in a word application, but that way is not so efficient. 

    Wednesday, December 17, 2014 7:43 AM

Answers

  • Hi kafuka88,

    What type is your application? Is it VBA for Excel/Word application, or some other applications like Excel/Word Add-In, or stand alone desktop or web application? What language does it use?

    If you want to use OLEDB data provider to connect to Excel, we can only export the table data, the table or cell format will be gone. The best way to export both the data and the format to the word document is to automate Excel application via VBA or Excel PIA. Here I made an example for you. Call this Excel VBA method, it'll export the tables to Word document.

    Sub ExportTablesToWord()
        'Add reference to Microsoft Word Object Library
        Dim wordApp As Word.Application
        Dim wordDoc As Word.Document
        
        Set wordApp = New Word.Application
        wordApp.Visible = True
        Set wordDoc = wordApp.Documents.Open("D:\files\OfficeDev\test files\test.docx")
    
        For i = 1 To Sheet1.ListObjects.Count
            Sheet1.ListObjects(i).Range.Copy
            wordDoc.Range(0).Paste
        Next i
        
        wordDoc.Save
        wordApp.Quit
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 18, 2014 6:37 AM
    Moderator
  • Maybe this.

    Sub CopyWorksheetsToWord()
    ' requires a reference to the Word Object library:
    ' in the VBE select Tools, References and check the Microsoft Word X.X object library
    Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet
        Application.ScreenUpdating = False
        Application.StatusBar = "Creating new document..."
        Set wdApp = New Word.Application
        Set wdDoc = wdApp.Documents.Add
        For Each ws In ActiveWorkbook.Worksheets
            Application.StatusBar = "Copying data from " & ws.Name & "..."
            ws.UsedRange.Copy ' or edit to the range you want to copy
            wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
            wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste
            Application.CutCopyMode = False
            wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
            ' insert page break after all worksheets except the last one
            If Not ws.Name = Worksheets(Worksheets.Count).Name Then
                With wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range
                    .InsertParagraphBefore
                    .Collapse Direction:=wdCollapseEnd
                    .InsertBreak Type:=wdPageBreak
                End With
            End If
        Next ws
        Set ws = Nothing
        Application.StatusBar = "Cleaning up..."
        ' apply normal view
        With wdApp.ActiveWindow
            If .View.SplitSpecial = wdPaneNone Then
                .ActivePane.View.Type = wdNormalView
            Else
                .View.Type = wdNormalView
            End If
        End With
        Set wdDoc = Nothing
        wdApp.Visible = True
        Set wdApp = Nothing
        Application.StatusBar = False
    End Sub


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, December 19, 2014 12:00 AM

All replies

  • Hi kafuka88,

    What type is your application? Is it VBA for Excel/Word application, or some other applications like Excel/Word Add-In, or stand alone desktop or web application? What language does it use?

    If you want to use OLEDB data provider to connect to Excel, we can only export the table data, the table or cell format will be gone. The best way to export both the data and the format to the word document is to automate Excel application via VBA or Excel PIA. Here I made an example for you. Call this Excel VBA method, it'll export the tables to Word document.

    Sub ExportTablesToWord()
        'Add reference to Microsoft Word Object Library
        Dim wordApp As Word.Application
        Dim wordDoc As Word.Document
        
        Set wordApp = New Word.Application
        wordApp.Visible = True
        Set wordDoc = wordApp.Documents.Open("D:\files\OfficeDev\test files\test.docx")
    
        For i = 1 To Sheet1.ListObjects.Count
            Sheet1.ListObjects(i).Range.Copy
            wordDoc.Range(0).Paste
        Next i
        
        wordDoc.Save
        wordApp.Quit
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 18, 2014 6:37 AM
    Moderator
  • Maybe this.

    Sub CopyWorksheetsToWord()
    ' requires a reference to the Word Object library:
    ' in the VBE select Tools, References and check the Microsoft Word X.X object library
    Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet
        Application.ScreenUpdating = False
        Application.StatusBar = "Creating new document..."
        Set wdApp = New Word.Application
        Set wdDoc = wdApp.Documents.Add
        For Each ws In ActiveWorkbook.Worksheets
            Application.StatusBar = "Copying data from " & ws.Name & "..."
            ws.UsedRange.Copy ' or edit to the range you want to copy
            wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
            wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste
            Application.CutCopyMode = False
            wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
            ' insert page break after all worksheets except the last one
            If Not ws.Name = Worksheets(Worksheets.Count).Name Then
                With wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range
                    .InsertParagraphBefore
                    .Collapse Direction:=wdCollapseEnd
                    .InsertBreak Type:=wdPageBreak
                End With
            End If
        Next ws
        Set ws = Nothing
        Application.StatusBar = "Cleaning up..."
        ' apply normal view
        With wdApp.ActiveWindow
            If .View.SplitSpecial = wdPaneNone Then
                .ActivePane.View.Type = wdNormalView
            Else
                .View.Type = wdNormalView
            End If
        End With
        Set wdDoc = Nothing
        wdApp.Visible = True
        Set wdApp = Nothing
        Application.StatusBar = False
    End Sub


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, December 19, 2014 12:00 AM