Export Table To Word Excel VBA Macro

We often copy/paste data, tables, and charts, from Excel to Word documents. In this post, we see how to automate that process with VBA macros. The code below copies a range with data in Excel, and pastes the content as a table in a Word document. The process can repeat or loop to copy various tables. The macro can be leveraged to copy other elements such as charts.

Macro code:

  
  Sub ExportTableToWord()
 
      Dim wordApp As Object
      Set wordApp = CreateObject("Word.Application")
   
      Range("A1").CurrentRegion.Copy
   
      With wordApp
          .Visible = True
          .Documents.Add
          .Selection.Paste
          .ActiveDocument.PageSetup.Orientation = wdOrientPortrait
          .ActiveDocument.Tables(1).AutoFitBehavior wdAutoFitWindow
      End With
   
  End Sub
  

Macro explained:

• We declare an object variable used to create and store a Word document object. Then we need to create a Word application object and assign it to that variable using the Set statement. This process is known as object late binding.
• Alternatively, we can use early binding to create a Word application object in Excel. However, in that case we need to add the Microsoft Word Object Library to the references in Excel’s VB editor under Tools -> References. Then, we can simply declare the object variable as indicated below.

  Dim wordApp As New Word.Application

• Then we copy the data we intend to export from Excel to Word. There are several ways to do that. The code above targets the region with data around cell A1. CurrentRegion is a property of the Range object that returns a range delimited by blank cells. Alternatively, we could specify the exact range address, or use Selection and export only the data that has previously been selected.
• Now we target the Word application object to perform the following tasks:
• Show the Word application (make it visible).
• Add a blank Word document. Alternatively, we could target an existing document too.
• Paste the contents from Excel into the selected Word document.
• Set the page orientation of the active document to portrait. This fits the size of the table in this particular case. The orientation can also be set as landscape if needed.
• Fit the table to the width of the active document window. Alternatively, we could set a fixed width or auto fit the content.

We can also insert the table in a particular page and location of the Word document targeting the Document object, as well as formatting the table as needed addressing the relevant properties and methods of the Table object in Word. We are actually using Word VBA objects in Excel by linking the Microsoft Word Object Library or creating a Word Application object within the Excel workbook, with either early or late object binding.

This is how we Export A Table From Excel To Word using VBA macros.

Other examples:

• How to Add Button To Cell Right-Click Menu with Excel VBA macros
• How to Import Pictures From Google Drive UPDATE 2023 with Excel VBA macros
• How to Import Specific Cells From Google Sheets with Excel VBA macros