CODEWITHSUNDEEP
excelms-wordworksheetvba
coeurdange57
coeurdange57

Reputation: 743

VBA Macro - Export table data from Excel file to Word and create a section for each worksheet

I use a macro in VBA for exporting data coming from Excel into Word.

Sub ExportToWord()
    Set obj = CreateObject("Word.Application")
    obj.Visible = True
    Set newobj = obj.Documents.Add

    For Each ws In ActiveWorkbook.Sheets
        ws.UsedRange.Copy
        newobj.ActiveWindow.Selection.PasteExcelTable False, False, False
        newobj.ActiveWindow.Selection.InsertBreak Type:=7
    Next
        newobj.ActiveWindow.Selection.TypeBackspace
        newobj.ActiveWindow.Selection.TypeBackspace

    obj.Activate
    newobj.SaveAs Filename:=Application.ActiveWorkbook.Path & "\OLD\" & Split(ActiveWorkbook.Name, ".")(0)
End Sub

All data and table layout are retrieved in Word. The Procedure will copy usedRange of each Worksheet to Word and page break by each Worksheet.

I would like to update this script by putting the name of the worksheet just before the copy/paste data for each worksheet.

Could you tell me how to do that?

After updated the code:

Sub export_workbook_to_word()
    Dim sheetName As String
    Set obj = CreateObject("Word.Application")
    obj.Visible = True
    Set newobj = obj.Documents.Add

    For Each ws In ActiveWorkbook.Sheets
        sheetName = ws.Name
        ws.UsedRange.Copy
        newobj.ActiveWindow.TypeText ws.Name
        newobj.ActiveWindow.Selection.PasteExcelTable False, False, False
        newobj.ActiveWindow.Selection.InsertBreak Type:=7

    Next
        newobj.ActiveWindow.Selection.TypeBackspace
        newobj.ActiveWindow.Selection.TypeBackspace

    obj.Activate
    newobj.SaveAs Filename:=Application.ActiveWorkbook.Path & "\" & Split(ActiveWorkbook.Name, ".")(0)

End Sub

I obtain the error Object doesn't support this property or method on the line newobj.ActiveWindow.TypeText ws.Name

Could you please help me?

Upvotes: 0

Views: 1706

Answers (2)

coeurdange57
coeurdange57

Reputation: 743

In fact The line

newobj.ActiveWindow.Selection.InsertBreak Type:=7

inserts a page break: 7 corresponds to wdPageBreak. I inserted a next page section break by changing 7 to 2 (corresponding to wdSectionBreakNextPage).

Sections in Word don't have names. I insert the name of the worksheet above the table:

    newobj.ActiveWindow.Selection.TypeText sheetName
    newobj.ActiveWindow.Selection.Style = ActiveDocument.Styles(-2)
    newobj.ActiveWindow.Selection.TypeParagraph

Upvotes: 0

Sam Basso
Sam Basso

Reputation: 127

Try adding this to declarations:

    Dim sheetName as String

And this below the For Each statement

    sheetName = ws.Name
    newobj.ActiveWindow.TypeText (sheetName)

Upvotes: 0

Related Questions