Extract data from EXCEL to WORD

[antefjante]

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?

 

[Sektor]

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

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

 

[antefjante]

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

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

 

[Sektor]

You could add file selector:

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

 

[Macropod]

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.

 

[Gravitas]

• 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.