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!

 

[S_Wish]

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

 ... Application.ActiveWorkbook.Path &

"\" & ActiveSheet.Name 

 

[UncleBajubjubs]

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]

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]

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]

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]

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]

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]

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]

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.