Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-29-2022, 05:09 PM
Jen75 Jen75 is offline Feeding a Word Document from Excel Source (not a mail merge) Windows 10 Feeding a Word Document from Excel Source (not a mail merge) Office 2016
Novice
Feeding a Word Document from Excel Source (not a mail merge)
 
Join Date: Sep 2022
Posts: 4
Jen75 is on a distinguished road
Question Feeding a Word Document from Excel Source (not a mail merge)

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
Reply With Quote
  #2  
Old 09-29-2022, 07:20 PM
Guessed's Avatar
Guessed Guessed is offline Feeding a Word Document from Excel Source (not a mail merge) Windows 10 Feeding a Word Document from Excel Source (not a mail merge) Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,969
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

We still can't see any attachments...
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 09-30-2022, 08:03 AM
macropod's Avatar
macropod macropod is offline Feeding a Word Document from Excel Source (not a mail merge) Windows 10 Feeding a Word Document from Excel Source (not a mail merge) Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Jen75 View Post
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.
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]
Reply With Quote
  #4  
Old 09-30-2022, 10:46 AM
Jen75 Jen75 is offline Feeding a Word Document from Excel Source (not a mail merge) Windows 10 Feeding a Word Document from Excel Source (not a mail merge) Office 2016
Novice
Feeding a Word Document from Excel Source (not a mail merge)
 
Join Date: Sep 2022
Posts: 4
Jen75 is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 09-30-2022, 10:54 AM
Jen75 Jen75 is offline Feeding a Word Document from Excel Source (not a mail merge) Windows 10 Feeding a Word Document from Excel Source (not a mail merge) Office 2016
Novice
Feeding a Word Document from Excel Source (not a mail merge)
 
Join Date: Sep 2022
Posts: 4
Jen75 is on a distinguished road
Default Attachments Uploaded

Hi Andrew,

You are right. I'm sorry. I thought I uploaded the attachments, but did not. I just added them now.
Attached Files
File Type: docx Sample Word Document.docx (11.3 KB, 12 views)
File Type: xlsx Sample Excel Source.xlsx (7.8 KB, 11 views)
File Type: docx Desired Result.docx (11.3 KB, 11 views)
Reply With Quote
  #6  
Old 09-30-2022, 04:04 PM
macropod's Avatar
macropod macropod is offline Feeding a Word Document from Excel Source (not a mail merge) Windows 10 Feeding a Word Document from Excel Source (not a mail merge) Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Jen75 View Post
The content does differ in all of the pages of the Word document. Each page is unique to a single person.
In that case, it would have been preferable to have the variable content in the data source also.

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
As coded, the macro assumes the:
• 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]
Reply With Quote
  #7  
Old 10-02-2022, 11:57 AM
Jen75 Jen75 is offline Feeding a Word Document from Excel Source (not a mail merge) Windows 10 Feeding a Word Document from Excel Source (not a mail merge) Office 2016
Novice
Feeding a Word Document from Excel Source (not a mail merge)
 
Join Date: Sep 2022
Posts: 4
Jen75 is on a distinguished road
Default

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!
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Feeding a Word Document from Excel Source (not a mail merge) 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:59 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft