Merge field source data field

macropod · #4 03-22-2013, 05:07 PM

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.

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