# VBA Code to Export a Worksheet as a Word Document



## UncleBajubjubs (Oct 31, 2017)

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!


----------



## S_Wish (Oct 31, 2017)

have not tested your code, but in preLast row I think should be written that:


```
... Application.ActiveWorkbook.Path &
```


```
"\" & ActiveSheet.Name
```


----------



## UncleBajubjubs (Oct 31, 2017)

Thanks,
Strangely though it is there in my code, I think maybe the text formatting of the forum made it not show on here.


----------



## Joe4 (Oct 31, 2017)

> Strangely though it is there in my code, I think maybe the text formatting of the forum made it not show on here.


Yes, I have seen that happen.

There is nothing wrong with the structure of that code, as far as I can see (nothing that would return that particular error message).
Is there any other VBA code in the module?
That is the kind of message I would expect to see if you have too many "End Sub" statements.


----------



## UncleBajubjubs (Oct 31, 2017)

Joe4 said:


> Yes, I have seen that happen.
> 
> There is nothing wrong with the structure of that code, as far as I can see (nothing that would return that particular error message).
> Is there any other VBA code in the module?
> That is the kind of message I would expect to see if you have too many "End Sub" statements.



Yup, I had been moving things around, and accidentally had a Sub started without an End Sub before this newest sub. 

Now it exports, but there are a few issues. The page it copies scales larger than intended, so what should be 2 pages in word is now 5. Any ideas on how to fix? Thanks!


----------



## Joe4 (Oct 31, 2017)

No, I don't do anything with Word VBA.  "Word" is a four letter word in my book, and I avoid it whenever possible.

I will move this to the "General Excel Discussion & Other Questions" forum.  We have a resident Word expert who answers a lot of Word question in that forum (so that is typically the best place to post questions regarding Word).


----------



## UncleBajubjubs (Oct 31, 2017)

Joe4 said:


> No, I don't do anything with Word VBA.  "Word" is a four letter word in my book, and I avoid it whenever possible.
> 
> I will move this to the "General Excel Discussion & Other Questions" forum.  We have a resident Word expert who answers a lot of Word question in that forum (so that is typically the best place to post questions regarding Word).



Alrighty, thank you!


----------



## UncleBajubjubs (Oct 31, 2017)

I've done more research and think if I remove the margins, it will be fixed, but am having difficulty in doing so. I found some code:

```
With WordApp.ActiveDocument.PageSetup
  .Orientation = wdOrientLandscape
  .TopMargin = WordApp.InchesToPoints(0.6)
  .BottomMargin = WordApp.InchesToPoints(0.6)
  .LeftMargin = WordApp.InchesToPoints(0.6)
  .RightMargin = WordApp.InchesToPoints(0.6)
End With
```

But am not able to get this to work, when added as 


```
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
    
With WordApp.ActiveDocument.PageSetup
.Orientation = wdOrientLandscape
.TopMargin = WordApp.InchesToPoints(0.6)
.BottomMargin = WordApp.InchesToPoints(0.6)
.LeftMargin = WordApp.InchesToPoints(0.6)
.RightMargin = WordApp.InchesToPoints(0.6)
End With


    newObj.SaveAs filename:=Application.ActiveWorkbook.Path & "" & ActiveSheet.Name
End Sub
```

Any thoughts?

Edit: Am I not doing the code tags right?


----------



## Macropod (Oct 31, 2017)

Try:

```
Sub Export_Excel_To_Word()
Dim wdApp As Object, wdDoc As Object, bStart As Boolean
bStart = False
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
On Error GoTo 0
If wdApp Is Nothing Then
  Set wdApp = CreateObject(Class:="Word.Application")
  bStart = True
End If

ActiveSheet.UsedRange.Copy

With wdApp
  .Visible = True
  'Create new Document
  Set wdDoc = .Documents.Add
  With wdDoc
    With .PageSetup
      .Orientation = 1 'wdOrientLandscape
      .TopMargin = wdApp.InchesToPoints(0.6)
      .BottomMargin = wdApp.InchesToPoints(0.6)
      .LeftMargin = wdApp.InchesToPoints(0.6)
      .RightMargin = wdApp.InchesToPoints(0.6)
    End With
    'Paste Data
    .Range.Paste
    .SaveAs2 Application.ActiveWorkbook.Path & "/" & ActiveSheet.Name & ".docx", 12, , , False ' 12 = wdFormatXMLDocument
    '.Close False
  End With
  'If bStart = True Then .Quit
End With
Application.CutCopyMode = False
'Clean up the Object when Finished
Set wdDoc = Nothing: Set wdApp = Nothing
End Sub
```
Note: I've added two lines of commented-out code for saving the document and, if your code starts the Word session, exiting Word. You can enable that code if you want.

As for the code tags, you create them by clicking on the # symbol on the reply menu. You then insert your code between them.


----------



## UncleBajubjubs (Nov 1, 2017)

Thank you, and for some reason it was showing me the code tags instead of just performing the formatting. I put in your solution, which works other than 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. I was also trying to set the line and paragraph spacing, it seems to automatically put large spacing in. I found a helpful example, which was


```
With Selection.ParagraphFormat
  .LineSpacingRule = wdLineSpaceAtLeast 
 .LineSpacing = 24
EndWith
```


on another site, but am not able to figure out how best to implement it in my code. I want to be able to set the paragraphs to not have big gaps between them.


----------



## UncleBajubjubs (Oct 31, 2017)

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 (Nov 1, 2017)

UncleBajubjubs said:


> I commented out the line
> 
> ```
> .SaveAs2 Application.ActiveWorkbook.Path & "/" & ActiveSheet.Name & ".docx", 12, , False
> ...


Oops - a comma was missing. Try:

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


UncleBajubjubs said:


> 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


----------

