VBA Copy/Paste An Image, Text, & Table (Excel to Word)
Taking A Previous Post One Step Further
This will build from a post I previously wrote entitled Copy & Paste An Excel Table Into Microsoft Word With VBA. Please review that post before reading any further as the duplicated parts of the code will not be explained within this article. You will learn in this post how to copy & paste images, text, and an Excel table all at once. This functionality will essentially allow you to export your Excel data into a perfectly formatted Word document report. Below is a representation of how the end result will function.
The VBA Code To Create This Automation
Below is the code used to create the exporting functionality. Notice how there are very few modifications from the previous post’s code. These new modifications will be explained below the following VBA macro code.
Sub ExportExcelToWord()
'PURPOSE: Copy/Paste An Excel Table, Text, & Logo Image 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
Dim myText As Excel.Range
Dim myLogo As Excel.Shape
'Optimize Code
Application.ScreenUpdating = False
Application.EnableEvents = False
'Copy Data from Excel
Set tbl = ThisWorkbook.Worksheets(Sheet1.Name).ListObjects("Table1").Range
Set myText = ThisWorkbook.Worksheets(Sheet1.Name).Range("B4:B5")
Set myLogo = ThisWorkbook.Worksheets(Sheet1.Name).Shapes("Logo_Image")
'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
'Optimize Code
WordApp.Application.ScreenUpdating = False
'Make MS Word Visible and Active
WordApp.Visible = True
WordApp.Activate
'Create a New Document
Set myDoc = WordApp.Documents.Add
'Copy/Paste Logo
myLogo.Copy
myDoc.Paragraphs(myDoc.Paragraphs.Count).Range.Paste
'Spacing
myDoc.Paragraphs.Add
myDoc.Paragraphs.Add
myDoc.Paragraphs.Add
'Copy/Paste Text
myText.Copy
myDoc.Paragraphs(myDoc.Paragraphs.Count).Range.PasteAndFormat (wdFormatPlainText)
'Spacing
myDoc.Paragraphs.Add
myDoc.Paragraphs.Add
'Copy Excel Table Range
tbl.Copy
'Paste Table into MS Word
myDoc.Paragraphs(myDoc.Paragraphs.Count).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
WordApp.Application.ScreenUpdating = True
'Clear The Clipboard
Application.CutCopyMode = False
End SubSetting Variables To Each Excel Object
Notice how the code creates a variable for each object you want to copy over
- Shape Object for the logo image
- Range Object for the text
- ListObject for the table
This will simplify the references later on and makes it easy to figure out what exactly is being copied from Excel.
'Copy Data from Excel
Set tbl = ThisWorkbook.Worksheets(Sheet1.Name).ListObjects("Table1").Range
Set myText = ThisWorkbook.Worksheets(Sheet1.Name).Range("B4:B5")
Set myLogo = ThisWorkbook.Worksheets(Sheet1.Name).Shapes("Logo_Image")Counting The Total Amount of Paragraphs
Instead of manually inserting a numerical value for which paragraph to paste to, it is much easier to recount them alongside each paste. This ensures you are always pasting into the last (bottom) paragraph and prevents errors of referencing a higher paragraph number than what has been created (ie trying to paste to paragraph 6 when only 4 paragraphs exist). Keep an eye out for the below bolded snippet which is used a few times in the source code.
myDoc.Paragraphs(myDoc.Paragraphs.Count).Range.PastePasting Excel Range Text
While the pasting of the Excel table was covered in the previous post (Copy & Paste An Excel Table Into Microsoft Word With VBA) and the pasting of the logo image is pretty straightforward; the pasting of text from an Excel range is a little bit different. Calling on the regular Paste method results in using the MS Word default for pasting format. This is typically set to Keep Source Formatting unless you manually change the default pasting settings. To only keep the text, you need to call on the PasteAndFormat method and designate it to format in plain text (wdFormatPlainText).
'Copy/Paste Text
myText.Copy
myDoc.Paragraphs(myDoc.Paragraphs.Count).Range.PasteAndFormat (wdFormatPlainText)Paragraph Spacing
By adding paragraphs you are essentially mimicking hitting the Return (aka Enter) key in Microsoft Word. You can add however many paragraphs you need to format your report just the way you visually want it to look. In the example code, there was an instance where 3 paragraphs in a row were added and another instance where only 2 were added. If you need to add a large number of paragraphs, you can always create a loop similar to what is shown below.
'Spacing (add 20 paragraphs)
For x = 1 To 20
myDoc.Paragraphs.Add
Next xStart Being Creative And Automate!
Hopefully, through this example, you have gained an appreciation for the capabilities of VBA macros across multiple applications (ie Excel to MS Word). I have been able to apply the concepts covered in this post to create monthly newsletters and reports within seconds in my financial analyst role. I’m sure there are many other examples out there showing how Excel to Word automation can be beneficial. If you have any examples from your own professional experience please share them with everyone in the comments section below. I am curious about what anyone else may be automating! I look forward to reading your thoughts!!
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.