none
Exporting in VB data from Excel to Word with creation of sections RRS feed

  • Question

  • Hi,

    I created a macro in EXCEL for exporting Excel data in Word. It's working.

    Now I would like to export each worksheet in a separate section with the name of the workshett for each new section. I don't know how to implement that in my 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.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

    Could you please help me to do that?

    Thanks in advance for your help

    Regards,

                                  
    Friday, December 8, 2017 9:28 AM

Answers

  • The line

            newobj.ActiveWindow.Selection.InsertBreak Type:=7

    inserts a page break: 7 corresponds to wdPageBreak. You can insert a next page section break by changing 7 to 2 (corresponding to wdSectionBreakNextPage).

    Sections in Word don't have names. You could 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


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, December 8, 2017 12:01 PM