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 Sub
Setting 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.
Pasting 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).
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
Start 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!
Best Way To Paste Special Excel Objects As PowerPoint Images
Quality Matters I've written many times in the past on how the quality and formatting of your work can separate...
Copy & Paste An Excel Range Into PowerPoint With VBA
Why Do This? I've received a large number of questions on how to command other programs with VBA through Excel....
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.