Copy & Paste An Excel Table Into Microsoft Word With VBA

Copy & Paste An Excel Table Into Microsoft With VBA

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 Sub

Teaching 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 0

In 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.Activate

Controlling 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.Add

Copy 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!  

As always, in order to download this article's example file you will need to be a subscriber of my free tips newsletter. If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.

 
     Already Subscribed? Click HERE to log-in to the "Example Files" section

     Already Subscribed? Click HERE to log-in to the "Example Files" section

 

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)