This Excel VBA tutorial explains how to export Excel to Word.
You may also want to read:
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
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.
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.
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.
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.
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
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
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
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.Activatenewobj.SaveAs Filename:=Application.ActiveWorkbook.Path & "\" & Split(ActiveWorkbook.Name, ".")(0)
sh.Select
End Sub
Dear Wyman,
First of all, thank you for taking out your time to solve a problem of a random internet stranger. It means a lot to me.
I ran the code provided by you, got the following errors:
Errors: http://imgur.com/a/sYWsV
I have also uploaded my workbook, so that its easier for you to understand. I’ve not incorporated your code in this workbook.
Download Link: https://drive.google.com/file/d/0B4zXiAcJ1pQTX2ZHYXNiWlJqTXM/view?usp=sharing
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
I tried to run all of the macros above; I keep getting one error message after another. Are these macros intended only for Windows-based Office or are they supposed to work on Mac Office too? How would you tailor your code for MAC Excel to Word?