Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-20-2013, 01:14 PM
kckay kckay is offline Merge field source data field Windows 7 32bit Merge field source data field Office 2007
Novice
Merge field source data field
 
Join Date: May 2012
Posts: 6
kckay is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 03-21-2013, 12:57 AM
macropod's Avatar
macropod macropod is offline Merge field source data field Windows 7 64bit Merge field source data field Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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

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]
Reply With Quote
  #3  
Old 03-22-2013, 01:46 PM
kckay kckay is offline Merge field source data field Windows 7 32bit Merge field source data field Office 2007
Novice
Merge field source data field
 
Join Date: May 2012
Posts: 6
kckay is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 03-22-2013, 05:07 PM
macropod's Avatar
macropod macropod is offline Merge field source data field Windows 7 64bit Merge field source data field Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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

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
As the macro includes its own folder browser, it's a fairly simple matter to run it against the workbooks in different folders.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 03-25-2013, 11:06 AM
kckay kckay is offline Merge field source data field Windows 7 32bit Merge field source data field Office 2007
Novice
Merge field source data field
 
Join Date: May 2012
Posts: 6
kckay is on a distinguished road
Default

Paul,
Brilliant idea. Thank you so much. I am using your code and experimenting with the files. That is so awesome.

-KC
Reply With Quote
Reply

Thread Tools
Display Modes


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
Merge field source data field How can I revise Excel data source in merge? navysalad Mail Merge 6 01-07-2012 06:50 PM
Merge field source data field 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

Other Forums: Access Forums

All times are GMT -7. The time now is 10:12 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