#1
|
|||
|
|||
Merge field source data field
I have a project that uses Excel for data capture and then generates a letter based on a Word template.
I have an Excel workbook with a hidden worksheet containing all of the fields and data that I want to populate in a Word template. I am adding a new field in the Word template to be populated from Excel. I will modify the Excel workbook to create and populate the data. I have a bunch of existing Excel workbooks that will not have the new field. I want to be able to perform a check in the Word template to use the data in the Excel field IF the field exists (new workbooks). If the field does not exist (old workbooks), then use " " in the Word template. If I write the code as: {IF (MERGEFIELD FacilityName} <> "" "FacilityName" ""} seems like it is looking for the field named FacilityName in my Excel workbook (which may or may not exist). Is there a way to write what I am looking for in the mergefield code of the Word Template? |
#2
|
||||
|
||||
Hi kckay,
You can't reference a non-existant field in a mailmerge, at any level. Doing so will always generate errors when you complete the merge. Accordingly, you will need to either: • add the field name to all the workbooks; or • test each source file (eg via a macro) and delete the mergefield from the mailmerge main document before completing the merge. The second approach, of course, carries the risk that the updated mailmerge main document will then be saved without the mergefield and other merges using that mailmerge main document against data files that do include the field won't reflect that fact.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Paul,
Thank you for the assistance. I did not plan for the future and the Word template is not macro-enabled. As this tool is used by about 20 persons and requires modifying the Excel executable (for .dotm rather than .dotx), I will modify the template to be a manual replacement field (bold/italic/underline/red). I will have a code release in September and will then put the macro code in and populate the users' files with the modified template and executable code for the next inspection season. -KC |
#4
|
||||
|
||||
Hi kckay,
IMHO, you'd do better to add the field name to all the workbooks. Depending on your setup, this could be done via a macro in a few minutes. For example, the following Excel macro will add a new column heading to the first worksheet in every workbook in a folder (if that worksheet doesn't already have the column heading). If your mailmerge workbooks are in that format, it would a fairly simple matter to temporarily either: (a) move all of them into a folder; or (b) move the non-mailmerge workbooks out, for macro processing. Code:
Sub UpdateWorkbooks() Application.ScreenUpdating = False Dim strFolder As String, strFile As String, xlWkBk As Workbook, LCol As Long, i As Long, StrFld As String strFolder = GetFolder If strFolder = "" Then Exit Sub strFile = Dir(strFolder & "\*.xls", vbNormal) StrFld = "MyNewFieldName" While strFile <> "" Set xlWkBk = Workbooks.Open(Filename:=strFolder & "\" & strFile, AddToMRU:=False) With xlWkBk With .Sheets(1) LCol = .Cells.SpecialCells(xlCellTypeLastCell).Column For i = 1 To LCol If .Cells(1, i).Value = StrFld Then GoTo Done Next .Cells(1, LCol + 1).Value = StrFld End With Done: .Close SaveChanges:=True End With strFile = Dir() Wend Set xlWkBk = Nothing Application.ScreenUpdating = True End Sub Function GetFolder() As String Dim oFolder As Object GetFolder = "" Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0) If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path Set oFolder = Nothing End Function
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Paul,
Brilliant idea. Thank you so much. I am using your code and experimenting with the files. That is so awesome. -KC |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Field not showing in Pivot Table Field List | kmcovington | Excel | 0 | 10-26-2012 10:14 AM |
How can I revise Excel data source in merge? | navysalad | Mail Merge | 6 | 01-07-2012 06:50 PM |
Mail Merge - If a field is black, take the data from the row below? | mikeprent | Mail Merge | 5 | 01-05-2012 04:52 AM |
Formula to subtract one month from due date field in reminder field | ghumdinger | Outlook | 1 | 10-01-2011 12:09 AM |
Mail Merge data source on network drive | Brandi | Mail Merge | 0 | 06-13-2010 11:26 AM |