import text from excel into word

Question

i have a use case that I'm hoping to see if i can automate.

We have a word document that's about 100 pages long, in that word document we need to insert ~250 sentences at prescribed spots that get delivered to us in an excel file. currently the insertion process is manual with copy and pastes. Problem is quite often the excel file gets updated so we need to redo the copy and paste exercise

Wondering if it can be automated.

The data in the excel file comes out like this

Unique ID | Text

12345 | Jack and Jill went up the hill

12346 | The quick brown fox

What I'm hoping is being able to do something like insert the reference to the unique ID in the word document and then be able to refresh the text from the source excel file when a new one is available.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nullam non facilisis eros. {12345} Jack and Jill went up the hill Duis imperdiet facilisis nibh vel tristique. Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. {12346} The quick brown fox Morbi quis ex aliquam, luctus sem ut, aliquet elit. Cras nec imperdiet ligula. Proin vitae dolor et augue ultricies gravida eu in ante. Fusce vestibulum dapibus est, ac ultrices justo imperdiet ut. Vestibulum porttitor metus ut erat imperdiet ultricies. Nulla dictum consectetur velit, ut interdum magna pretium in.

Mail merge can do it but its a one time import time thing and can't preview the inserted sentences until I generate the final file. hoping there is another option

doug_robbins_word_mvp · Answer

wordnoob34572&nbsp;You could insert DOCVARIABLE fields in the document for each of the Unique ID's and the whenever you get a new file, use code the iterate through the Excel data, setting the value of each of the Variables in the document to the data in the column to the right of the Unique ID and then have the code update the fields in the document so that each field displayed the data.
This code would be run from Word
Dim xlapp As Object
Dim xlbook As Object
With Dim xlsheet As Object
Dim i As Long
Dim bStartApp as Boolean
Dim FD As FileDialog
Dim strSource As String
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
    .Title = "Select the workbook that contains the data"
    .Filters.Clear
    .Filters.Add "Excel Workbooks", "*.xlsx"
    .AllowMultiSelect = False
    If .Show = -1 Then
        strSource = .SelectedItems(1)
    Else
        MsgBox "You did not select the workbook that contains the data"
        Exit Sub
    End If
End With
bStartApp = False
On Error Resume Next
Set xlapp = GetObject(, "Excel.Application")
If Err Then
    bstartApp = True
    Set xlapp = CreateObject("Excel.Application")
End If
On Error GoTo 0
Set xlbook = xlapp.Workbooks.Open(strSource)
Set xlsheet = xlbook.Worksheets(1)
With xlsheet.Range("A1")
    For i = 1 to .CurrentRegion.Rows.Count - 1
        ActiveDocument.Variables(.Offset(i,0).Value = .Offset(i, 1)
    Next i
End WIth
xlbook.Close, False
If bStartApp = True Then
    xlapp.Quit
End If
Set xlapp = Nothing
Set xlbook = Nothing
With ActiveDocument
   .PrintPreview
   .ClosePrintPreview
End With
&nbsp;

Forum Discussion

import text from excel into word

1 Reply

Resources