Copy & Paste A Logo Image, Text, & Excel Table Into Microsoft Word With VBA

Copy & Paste A Logo Image, Text, & Excel Table Into Microsoft Word With VBA

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.

 myDoc.Paragraphs(myDoc.Paragraphs.Count).Range.Paste

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 straight forward; 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 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 x

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 it with everyone in the comments section below.  I am curious of what anyone else may be automating!  I look forward to reading your thoughts!!

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 :)

A brand new Excel add-in that will automate creating PowerPoint slides from your Excel data! Click the image to learn more! #FreeTrial

A brand new Excel add-in that will automate creating PowerPoint slides from your Excel data! Click the image to learn more! #FreeTrial