![]() |
|
#1
|
|||
|
|||
|
Hi, I have a Word Document consisting of over 600 pages. Each page is for a different person. There is one field in the Word Document that I would like to fill in the corresponding field in an Excel spreadsheet. Values are different for each person. I have a name field in the Word Document, and it matches with the name field in the Excel file. I was hoping I could get Word to autofill the Excel value based on a name match, but I can't seem to figure out if this is possible and how to do it. I've researched mail merging, but this is different because I'm starting with an existing document and not a blank template. Recreating this as a blank template in order to use a mail merge is not an option. The only other alternative I see is to copy and paste the field for 600+ records from Excel to Word, and I'd like to avoid that if possible. I've attached a sample of the Word Document, the Excel file, and the Desired Result of what I'm trying to accomplish. Any advice would be appreciated. Thank you! Last edited by Jen75; 09-29-2022 at 05:11 PM. Reason: Didn't see attachment |
|
#2
|
||||
|
||||
|
We still can't see any attachments...
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
|
#3
|
||||
|
||||
|
Unless the content of all those letters differ, all you need for a mailmerge is to delete delete all except the first letter then, using a standard 'letter' mailmerge, connect to your Excel datasource and replace whoever's name you have there with a mergefield pointing to the relevant column header.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
#4
|
|||
|
|||
|
Thank you so much for the response!
The content does differ in all of the pages of the Word document. Each page is unique to a single person. |
|
#5
|
|||
|
|||
|
Hi Andrew,
You are right. I'm sorry. I thought I uploaded the attachments, but did not. I just added them now. |
|
#6
|
||||
|
||||
|
Quote:
However, since you well and truly passed that point, try the following macro: Code:
Sub Demo()
Application.ScreenUpdating = False
Dim xlApp As Object, xlWkBk As Object, StrWkBkNm As String, StrWkSht As String
Dim iDataRow As Long, xlFndList As String, xlRepList As String, i As Long
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Employees.xlsx"
StrWkSht = "Sheet1"
If Dir(StrWkBkNm) = "" Then
MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation
Exit Sub
End If
On Error Resume Next
'Start Excel
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
MsgBox "Can't start Excel.", vbExclamation
Exit Sub
End If
On Error GoTo 0
With xlApp
'Hide our Excel session
.Visible = False
' The file is available, so open it.
Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm, ReadOnly:=True, AddToMru:=False)
If xlWkBk Is Nothing Then
MsgBox "Cannot open:" & vbCr & StrWkBkNm, vbExclamation
.Quit
Exit Sub
End If
' Process the workbook.
With xlWkBk
'Ensure the worksheet exists
If SheetExists(xlWkBk, StrWkSht) = True Then
With .Worksheets(StrWkSht)
' Find the last-used row in column A.
iDataRow = .Cells(.Rows.Count, 1).End(-4162).Row ' -4162 = xlUp
' Capture the F/R data.
For i = 1 To iDataRow
' Skip over empty fields to preserve the underlying cell contents.
If Trim(.Range("A" & i)) <> vbNullString Then
xlFndList = xlFndList & "|" & Trim(.Range("A" & i))
xlRepList = xlRepList & "|" & Trim(.Range("B" & i))
End If
Next
End With
Else
MsgBox "Cannot find the designated worksheet: " & StrWkSht, vbExclamation
End If
.Close False
End With
.Quit
End With
' Release Excel object memory
Set xlWkBk = Nothing: Set xlApp = Nothing
'Exit if there are no data
If xlFndList = "" Then Exit Sub
With ActiveDocument.Range.Find
.ClearFormatting
.Replacement.ClearFormatting
.Wrap = wdFindContinue
.MatchWholeWord = True
.Text = "^w^p"
.Replacement.Text = "^p"
.Execute Replace:=wdReplaceAll
'Process each string from the List
For i = 1 To UBound(Split(xlFndList, "|"))
.Text = Split(xlFndList, "|")(i) & vbCr & "ID Field:"
.Replacement.Text = "^& " & Split(xlRepList, "|")(i)
.Execute Replace:=wdReplaceAll
Next
End With
Application.ScreenUpdating = True
End Sub
Function SheetExists(xlWkBk As Object, SheetName As String) As Boolean
Dim i As Long: SheetExists = False
For i = 1 To xlWkBk.Sheets.Count
If xlWkBk.Sheets(i).Name = SheetName Then
SheetExists = True: Exit For
End If
Next
End Function
• data are in a workbook named 'Employees.xlsx' in your documents folder; • data are in a worksheet named 'Sheet1'; • employee names in the document are found in column A of the worksheet; and • employee IDs to be used in the document are found in column B of the worksheet. You can edit the code to match whatever your actual scenario is. I note that your sample document is inconsistent regarding the presence of a space after 'ID Field:'. The macro has been coded to work around that by eliminating any white-space before paragraph breaks, then reinserting a space before the ID.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
#7
|
|||
|
|||
|
Yes, that worked! I think this is a solution that will work well for what I need to do. Thank you so much for taking the time to help me!
|
|
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
Customize document content based on Mail Merge source fields?
|
mallorious | Mail Merge | 5 | 09-22-2020 03:04 PM |
| Creating a table in one document of WORD from EXCEL with Mail Merge | Joseph.Comerford@bentley. | Mail Merge | 1 | 04-18-2015 01:19 AM |
| Merge fields in Word and source data from Excel | SteveVai | Mail Merge | 3 | 03-13-2014 05:25 AM |
| Data from hidden internal tables feeding listboxes in same Word Document | marksm33 | Word VBA | 2 | 02-21-2014 07:10 PM |
| Mail Merge using Word 2010 - Header source not be recognized | Gage262 | Word | 8 | 02-01-2012 11:08 AM |