VBA Express : Multiple Apps - Paste Data From Excel To Word

Multiple Apps

Paste Data From Excel To Word

Ease of Use

Intermediate

Version tested with

2002

Submitted by:

Jacob Hilderbrand

Description:

Take data from your excel spreadsheet and programatically paste it into a new Word document

Discussion:

You have a bunch of data in Excel, but you want to put some of it into Word. Maybe you input a lot of data into Excel, but you have to make a weekly report with just some of the data and put it in Word. This code will paste the data in automatically and also give you an idea of how to control Word from Excel.

Code:

instructions for use

			Option Explicit 
 
Sub PasteToWord() 
     
    Dim AppWord         As Word.Application 
     
    Set AppWord = CreateObject("Word.Application") 
    AppWord.Visible = True 
     '  Change the range to suit your needs. See the How to Use for this code
    Sheets("Sheet1").Range("A1:C10").Copy 
    AppWord.Documents.Add 
    AppWord.Selection.Paste 
     
    Application.CutCopyMode = False 
     
    Set AppWord = Nothing 
     
End Sub

How to use:

Open Excel.
Press Alt + F11 to open VBE.
Insert | Module.
Paste the code there.
Tools | References - Check to make sure that "Microsoft Word 10.0" is checked; if not then add the reference (Note that the number 10.0 may be different based on your version of Excel).
Change this line <<Sheets("Sheet1").Range("A1:C10").Copy>> to the sheet and range you would want to copy.
Close VBE (Alt + Q or press the X in the top right corner).
From the Tools menu select Macro | Macros
Select "PasteToWord" and press Run

Test the code:

Refer to the "How To Use" section
Download the attachment for a working example

Sample File:

PasteToWord.ZIP 7.56KB

Approved by mdmackillop

This entry has been viewed 265 times.