Excel VBA export Excel to Word

This Excel VBA tutorial explains how to export Excel to Word.

You may also want to read:

Export Excel to PDF

Excel VBA export Excel to Word

In the previous post, I demonstrated how to export Excel to PDF, which is very easy because there is already a built in function to do that (using Save As and choose PDF). However, there is no built in function to export Excel to Word. Fortunately Excel cell is actually a table in Word, we can simply copy the cells and paste to Word. This tutorial explains how to do it automatically using Excel VBA to export Excel to Word.

Excel VBA export Excel to Word (Single Worksheet)

Excel has about 1M rows and 16k columns, we cannot simply export the whole spreadsheet to Word. So the first question to think about is, what Range do we need to export to Word? My recommendation is to export all UsedRange. You may also consider to reset UsedRange before copy as explained in my previous post.

Insert the following Procedure in Excel Module

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

For example, we have the below worksheet

export Excel to Word 01

 

Run the Macro, and the below new Word document will pop up. The Word document is automatically saved as the Worksheet name under the same folder of the Workbook.

export Excel to Word 02

 

It is possible that the imported table length is too wide to display in Word, you can also use Word VBA Table.AutoFitBehavior Method to auto fit the table, which has the same effect of AutoFit in Word as below.

export Excel to Word 03

Run the below macro in Word VBA to loop through all tables in Word document to autosize.

Sub autoSizeTbl()
    For Each tbl In ActiveDocument.Tables
        tbl.AutoFitBehavior wdAutoFitContent
    Next
End Sub

Ideally this Macro can be run from Excel VBA, unfortunately I can’t get it work properly.

Excel VBA export Excel to Word (Multiple Worksheets)

Assume that we have Sheet1, Sheet2, Sheet3 in a Workbook “Export.xlsm”, we want to export all three worksheets to a single workbook.

export Excel to Word 04

 

Insert a Excel Module and paste the below Procedure.

The below Procedure will copy usedRange of each Worksheet to Word and page break by each Worksheet. Finally save and name the Word document as the Workbook name.

Sub export_workbook_to_word()
    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 & "\" & Split(ActiveWorkbook.Name, ".")(0)

End Sub

 

Run the Macro, a Word document called “Export.docx” pops up. Worksheet1 is pasted in page 1, Worksheet2 is pasted in page 2, Worksheet3 is pasted in page 3.

export Excel to Word 05

export Excel to Word 06

export Excel to Word 07

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

6 thoughts on “Excel VBA export Excel to Word

  1. Dear Wyman,

    This is just the kind of solution that I was looking for.

    However, there are some little tweakings which are required, do you feel you could help me out with the

    same?

    For example:

    1. The tables / ranges I wish to convert are spread across different sheets in the same worksheets.

    2. I already have a code in place, which takes different ranges of cells from excel and coverts them to pdf.

    Could the same be done to export it to Word?

    3. In the exported word, the margins seem to be used as default template, could the same be changed?

    Thanks

    1. Hi ST,

      1. Not sure what you mean, if you are saying you have different tables in different worksheets, my last Macro should do the job

      2. If you want to modify your existing code, just remove all the PDF export related method from your codes that look like this, and then replace with the below code.

      Set obj = CreateObject("Word.Application")
      Set newObj = obj.Documents.Add
      newObj.SaveAs Filename:=Application.ActiveWorkbook.Path & "\" & ActiveSheet.Name

      3. You can change the Word margin using VBA, the value in brackets is the length in cm
      With ActiveDocument.PageSetup
      .TopMargin = CentimetersToPoints(2)
      .BottomMargin = CentimetersToPoints(2)
      .LeftMargin = CentimetersToPoints(2)
      .RightMargin = CentimetersToPoints(2)
      End With

  2. Hi Wyman,

    Thanks for the reply.

    I’m a VBA noob. Somehow with difficulty I had compiled the following code, which opens the print dialog box, and prints the selected range of sheets and range.

    Could you help me in positioning your above code into this code?

    Sub Newpdff()
    Range(“C177”).Select
    Selection.Copy
    Range(“C179”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Dim s As String, ary, a, sh As Worksheet

    Set sh = ActiveSheet
    s = Sheets(“InputSheet”).Range(“C179”).Text
    ary = Split(s, “,”)

    For Each a In ary
    Sheets(a).Select
    Sheets(a).Activate
    Sheets(a).Range(ActiveSheet.PageSetup.PrintArea).Select
    Next a

    ThisWorkbook.Sheets(ary).Select

    Application.Dialogs(xlDialogPrint).Show

    sh.Select
    End Sub

    1. Hi ST, I ran your code with error, so I made some modification. I hope you get what you need.

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

      Range("C177").Select
      Selection.Copy
      Range("C179").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

      Dim s, ary() As String, a, sh As Worksheet

      Set sh = ActiveSheet
      s = Sheets("InputSheet").Range("C179").Text
      ary = Split(s, ",")

      For i = 0 To UBound(ary())

      Sheets(CInt(ary(i))).Select
      Sheets(CInt(ary(i))).Activate
      Sheets(CInt(ary(i))).Range(ActiveSheet.PageSetup.PrintArea).Copy
      newobj.ActiveWindow.Selection.PasteExcelTable False, False, False
      newobj.ActiveWindow.Selection.InsertBreak Type:=7
      Next i

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

    1. Hi ST, I tested and the below (Newpdff2) will work. One thing to note is that somehow I cannot get the Word autofit VBA to run from Excel, so after running the below Macro, run another Macro (Macro2) in the Word VBA.

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

      Range("C177").Select
      Selection.Copy
      Range("C179").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

      Dim s, ary() As String, a, sh As Worksheet

      Set sh = ActiveSheet
      s = Sheets("InputSheet").Range("C179").Text
      ary = Split(s, ",")

      For i = 0 To UBound(ary())

      Sheets(ary(i)).Select
      Sheets(ary(i)).Activate
      Sheets(ary(i)).Range(ActiveSheet.PageSetup.PrintArea).Copy
      newobj.ActiveWindow.Selection.PasteExcelTable False, False, False
      newobj.ActiveWindow.Selection.InsertBreak Type:=7
      Next i

      For Each t In newobj.Tables
      t.AutoFitBehavior wdAutoFitContent
      Next
      newobj.Activate

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

      Sub Macro2()
      For Each tbl In ActiveDocument.Tables
      tbl.AutoFitBehavior wdAutoFitContent
      Next
      End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *