VBA Code to Export a Worksheet as a Word Document

[UncleBajubjubs]

Hello, I have a workbook with one worksheet which is intended to gather data from the other sheets, and then be exported as a word document. I don't have much experience with this, so i copied some code I found online:

Sub export_excel_to_word()
    Set obj = CreateObject("Word.Application")
    obj.Visible = True
    Set newObj = obj.Documents.Add
    ActiveSheet.UsedRange.Copy
    newObj.Range.Paste
    Application.CutCopyMode = False
    obj.Activate
    newObj.SaveAs Filename:=Application.ActiveWorkbook.Path & "" & ActiveSheet.Name
End Sub

But this gives me "Unexpected End Sub" on the first line. Any ideas, or just alternate code which will do what I need? Thanks!

 

[Macropod]

I commented out the line

  .SaveAs2 Application.ActiveWorkbook.Path & "/" & ActiveSheet.Name & ".docx", 12, , False

As it was giving me type mismatch, but otherwise it all works.

Oops - a comma was missing. Try:

.SaveAs2 Application.ActiveWorkbook.Path & "/" & ActiveSheet.Name & ".docx", 12, , , False ' 12 = wdFormatXMLDocument

Original code updated.

I was also trying to set the line and paragraph spacing, it seems to automatically put large spacing in.

In that that case, since Word's default for an empty document is to use the 'Normal' Style, all you should need to do is insert:

    'Spacing defaults
    With .Styles("Normal").ParagraphFormat
      .SpaceBefore = 0
      .SpaceAfter = 0
      .LineSpacingRule = 0 'wdLineSpaceSingle
    End With

before:
'Paste Data