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.