VBA To Copy/Paste Excel Table Into Microsoft Word
Moving An Excel Table Into A New Word Document
In this post you will learn how to write VBA code to automate copy and pasting an Excel table range into a new Word document. The article should provide a good foundation to allow you to create a personalized VBA macro that suites your specific task needs. Maybe you need to copy over an Excel range or paste your table after paragraph 3 in a Word document. With minor tweaks to the following code you should be able to accomplish all of this with a little further research (aka ‘googling’). Below is the macro code you can insert into an Excel VBA module and run. Right below the VBA code I will provide a little more detail on what the macro routine is doing so you can fully understand its makeup.
Sub ExcelRangeToWord()
'PURPOSE: Copy/Paste An Excel Table Into a New Word Document
'NOTE: Must have Word Object Library Active in Order to Run _
(VBE > Tools > References > Microsoft Word 12.0 Object Library)
'SOURCE: www.TheSpreadsheetGuru.com
Dim tbl As Excel.Range
Dim WordApp As Word.Application
Dim myDoc As Word.Document
Dim WordTable As Word.Table
'Optimize Code
Application.ScreenUpdating = False
Application.EnableEvents = False
'Copy Range from Excel
Set tbl = ThisWorkbook.Worksheets(Sheet1.Name).ListObjects("Table1").Range
'Create an Instance of MS Word
On Error Resume Next
'Is MS Word already opened?
Set WordApp = GetObject(class:="Word.Application")
'Clear the error between errors
Err.Clear
'If MS Word is not already open then open MS Word
If WordApp Is Nothing Then Set WordApp = CreateObject(class:="Word.Application")
'Handle if the Word Application is not found
If Err.Number = 429 Then
MsgBox "Microsoft Word could not be found, aborting."
GoTo EndRoutine
End If
On Error GoTo 0
'Make MS Word Visible and Active
WordApp.Visible = True
WordApp.Activate
'Create a New Document
Set myDoc = WordApp.Documents.Add
'Copy Excel Table Range
tbl.Copy
'Paste Table into MS Word
myDoc.Paragraphs(1).Range.PasteExcelTable _
LinkedToExcel:=False, _
WordFormatting:=False, _
RTF:=False
'Autofit Table so it fits inside Word Document
Set WordTable = myDoc.Tables(1)
WordTable.AutoFitBehavior (wdAutoFitWindow)
EndRoutine:
'Optimize Code
Application.ScreenUpdating = True
Application.EnableEvents = True
'Clear The Clipboard
Application.CutCopyMode = False
End SubTeaching Excel The MS Word Language
In order to control Microsoft Word from inside Excel’s Visual Basic Editor, we need to teach Excel how to speak in Word’s own terminology. MS Word uses a vocabulary containing terms such as “Document” and “Paragraph” that simply do not exist in Excel’s own code language. You can learn more about this topic by reading my post Teaching Excel To Speak PowerPoint. In order to activate MS Word’s object library, simply click the Tools menu in the Visual Basic Editor and select Reference. Then make sure the reference Microsoft Office Word 12.0 Object Library is checked. In the below image I show you exactly how to do this!
Opening MS Word From Inside Excel
Now that we have taught Excel how to communicate with Microsoft Word, let’s look at how we can accomplish pasting an Excel table into a brand new Word document. Near the beginning of the code there is a section that looks like this:
'Create an Instance of MS Word
On Error Resume Next
'Is MS Word already opened?
Set WordApp = GetObject(class:="Word.Application")
'Clear the error between errors
Err.Clear
'If MS Word is not already open then open MS Word
If WordApp Is Nothing Then Set WordApp = CreateObject(class:="Word.Application")
'Handle if the Word Application is not found
If Err.Number = 429 Then
MsgBox "Microsoft Word could not be found, aborting."
GoTo EndRoutine
End If
On Error GoTo 0In this part of the code we are determining if Microsoft Word is open or not. If Word is already open, we can set a variable equal to the entire program by using GetObject. If MS Word is not currently running we can use CreateObject to run an instance of Word and then set a variable equal to that specific instance of MS Word.
When using CreateObject, the target application will start running but it is not visible on screen. Therefore we need to turn the Visible setting on (equal to true). Also, VBA with Word is a little bit different than with Excel in that it is much more dependent on its window showing on screen. Therefore a second command must be written to Activate Microsoft Word.
'Make MS Word Visible and Active
WordApp.Visible = True
WordApp.ActivateControlling MS Word From Inside Excel
Now that you are 100% sure Word is running, you can begin to control it! Let’s first command Word to make a brand new document to paste your table to. Along the way it is important to set a variable during each stage of creation so that if you ever want to reference the document later on you can do so with your respective variable.
'Create a New Document
Set myDoc = WordApp.Documents.AddCopy From Excel, Paste Onto Document
Now that you have a new document created, you can command Excel to paste your table into MS Word. Near the beginning of the code, there was a line that allowed you to specify the exact table you wanted to copy. The variable tbl was used to remember this table range and to allow you to reference the range later on in the code.
Guru Tip: It is a good idea to place code that may need to be manually changed at some point in the future near the beginning of the subroutine. This prevents you from having to scroll through your code and pinpoint the exact place where you spelled out which range you wanted to copy or which worksheet you wanted to pull data from. This can save you a bunch of time and prevent confusion!
Word has a special method called PasteExcelTable, which (as you can guess) allows you paste in an Excel table. There are three variables you can tweak to get you table looking and functioning just the way you want.
- LinkedToExcel – True links the pasted table to the original Excel file so that changes made to the Excel file are reflected in Microsoft Word.
- WordFormatting – True formats the table using the formatting in the Word document. False formats the table according to the original Excel file.
- RTF – True pastes the Excel table using Rich Text Format (RTF). False pastes the Excel table as HTML.
Now for the last step! Depending on how large your table is, it may be spilling outside of your document page. In order to prevent this from happening you can go ahead and use AutoFitBehavior to resize the table to fit perfectly inside your Word document.
'Copy Excel Table Range
tbl.Copy
'Paste Table into MS Word
myDoc.Paragraphs(1).Range.PasteExcelTable _
LinkedToExcel:=False, _
WordFormatting:=False, _
RTF:=False
'Autofit Table so it fits inside Word Document
Set WordTable = myDoc.Tables(1)
WordTable.AutoFitBehavior (wdAutoFitWindow)Some Snippets For Possible Code Modifications
Robert K asks, “What if I want I want my macro to copy the table to a specific Word Document?”
'Change: [Set myDoc = WordApp.Documents.Add] to:
Set myDoc = WordApp.Documents.Open("C:\Users\chris\Desktop\Document Name.docx")David C asks, “How do I paste the table into a specific line on the Word Document?”
'Change: [myDoc.Paragraphs(1).Range.PasteExcelTable _] to:
myDoc.Paragraphs(6).Range.PasteExcelTable _How Can You Use These Concepts?
I want to hear from you and learn how you might use this VBA code in your workflow. Also, what types of modifications would you make to fit your needs? I look forward to reading your thoughts in the comment section below!
Download Example Excel File
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.