![]() |
#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!
|
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
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 |