Extract data from EXCEL to WORD

antefjante

New Member
Joined
Oct 30, 2016
Messages
19
Im looking for a way to extract data from EXCEL worksheet in to Word. i want to place the info in various places including the header and footer.
I want to use relative locations so i can move around my folder with all my documents. When i open a word file i want the info to update automatically from my "master file" which is in excel format.

Does enyone have a good tip to accomplish this?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Well, there're several approaches.
First, you could use placeholders and search for them.
Second, you could use bookmarks, which is much efficient than looking for placeholder, but requires some preparing.
Here's a little example (Tools->References->Microsoft Word 14.0 Object Library and also the presence of bookmark "my_bookmark)":
Code:
Sub F()


    Dim wdApp As Word.Application
    Dim doc As Word.Document
    
    Set wdApp = New Word.Application
    Set doc = wdApp.Documents.Open("PATH_TO_WORD_FILE")
    
    '// Replace bookmark with cell's value
    doc.Bookmarks("my_bookmark").Range.Text = Sheets("Sheet1").Range("A1").Value


End Sub
 
Last edited:
Upvote 0
Is it possible to do it the other way around since my word files might change name..

Well, there're several approaches.
First, you could use placeholders and search for them.
Second, you could use bookmarks, which is much efficient than looking for placeholder, but requires some preparing.
Here's a little example (Tools->References->Microsoft Word 14.0 Object Library and also the presence of bookmark "my_bookmark)":
Code:
Sub F()


    Dim wdApp As Word.Application
    Dim doc As Word.Document
    
    Set wdApp = New Word.Application
    Set doc = wdApp.Documents.Open("PATH_TO_WORD_FILE")
    
    '// Replace bookmark with cell's value
    doc.Bookmarks("my_bookmark").Range.Text = Sheets("Sheet1").Range("A1").Value


End Sub
 
Last edited:
Upvote 0
You could add file selector:
Code:
Sub F()


    Dim sFileName As String
    Dim wdApp As Word.Application
    Dim doc As Word.Document
    
    With Application.FileDialog(msoFileDialogFilePicker)
        If .Show() Then sFileName = .SelectedItems(1) Else Exit Sub
    End With
    
    Set wdApp = New Word.Application
    Set doc = wdApp.Documents.Open(sFileName)
    
    '// Replace bookmark with cell's value
    doc.Bookmarks("my_bookmark").Range.Text = Sheets("Sheet1").Range("A1").Value


End Sub
 
Upvote 0
The simplest way is to copy & paste the data from Excel to Word using the 'paste link' method. That way, provided you don't move the Excel workbook, the documents can be moved around but will remain linked to it. The links can also be set to auto-update so they'll always reflect the workbook's current state. The links can be made to specified cells/ranges or to named ranges (which can change scope and have those changes reflected in the Word documents). No code required.
 
Upvote 0
  • Open the Excel worksheet from which you want to extract data as well as the Word document into which you want to insert or embed the data.
  • Select the chart, range or other data in Excel that you want to use in Word. Press "Ctrl-C" or click on "Copy" in the Clipboard group of the Home tab.
  • Go to the Word document. Click the "Paste" drop-down arrow in the Clipboard group of the Home tab and select "Paste Special."
  • Choose "Paste" and select "Microsoft Office Excel Chart Object" to embed the data. Choose "Paste Link" and select "Microsoft Office Excel Chart Object" to link the data.
  • Click "OK" to insert the extracted data from Excel.:)


 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top