VBA:Export Excel Table in Word Document

[Susanto3311]

hi all..
i found code to export table to ms word document but not work for me
this code here

Code: Select all

Sub ExportExcelTableinworddocument()
    Dim wdapp As New Word.Application
        On Error Resume Next
        wdapp.Visible = True
    wdapp.Activate
    wdapp.Documents.Add
    
    With wdapp.Selection
            .ParagraphFormat.Alignment = wdAlignParagraphCenter
        .BoldRun
        .Font.Size = 15
        .Font.ColorIndex = wdDarkRed
        .TypeText "MIS Report"
            End With
        Sheet1.Range("A1").CurrentRegion.Copy
        wdapp.Selection.Paste
    Application.CutCopyMode = False
    
    ActiveDocument.Tables(1).AllowAutoFit = True
    ActiveDocument.Tables(1).AutoFitBehavior wdAutoFitContent
    ActiveDocument.SaveAs2 "D:\test2\" & "ExportWord.docx"
    ActiveDocument.Close
    wdapp.Quit
    End Sub

this code erro show mesage in debug :Dim wdapp As New Word.Application
i want this code work too in any name sheet (active sheet) not only sheet1
i hope someone help me out..
thank in advance

susanto

[HansV]

The code uses so-called early binding. This requires you to set a reference to the Microsoft Word n.0 Object Library.
I have rewritten the code to use late binding, so that you do not need to set that reference.

Code: Select all

Sub ExportExcelTableinWordDocument()
    Dim wdApp As Object
    Dim wdDoc As Object
    Dim f As Boolean
    On Error Resume Next
    Set wdApp = GetObject(Class:="Word.Application")
    On Error GoTo ErrHandler
    If wdApp Is Nothing Then
        Set wdApp = CreateObject(Class:="Word.Application")
        f = True
    End If
    Set wdDoc = wdApp.Documents.Add
    With wdApp.Selection
        .ParagraphFormat.Alignment = 1 ' wdAlignParagraphCenter
        .BoldRun
        .Font.Size = 15
        .Font.ColorIndex = 13 ' wdDarkRed
        .TypeText "MIS Report"
        ActiveSheet.Range("A1").CurrentRegion.Copy
        Selection.Paste
        Application.CutCopyMode = False
    End With
    
    wdDoc.Tables(1).AllowAutoFit = True
    wdDoc.Tables(1).AutoFitBehavior 1 ' wdAutoFitContent
    wdDoc.SaveAs2 "D:\test2\ExportWord.docx"

ExitHandler:
    On Error Resume Next
    wdDoc.Close SaveChanges:=False
    If f Then
        wdApp.Quit SaveChanges:=False
    End If
    Exit Sub

ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

[Susanto3311]

hi Hans, working well, thank you so much.