asked on

Word 2007: Invalid Merge field

In word 2003 it is possible to insert a database, select an excel file, and use Query options to select the fields you want to insert. Word then inserts only the fields you have selected.
When I do this in Word 2007 it is a different story. After I have selected the fields I want, word inserts all the fields from the Excel database. If I select Insert data as field I get a dialog box stating Invalid Merge field and I have the options to delete the field from the original document. I get prompted to do this for all the fields I havent selected.

If I actually click on Remove Field repeatedly for all the excluded fields it will eventually insert the data I want, but its a lot of clicking when there are a few hundred columns/fields to exclude/remove.
This was really simple to do in 2003 and I would greatly appreciate if anyone have a solution to this problem.

Marius Jensen/334989

ScreenShot117.bmp
ScreenShot118.bmp

irudyk

Once you have connected your mail merge document to your data source, you could try running the following code to strip out all mail merge field names in hte document that do not exist in the data source.

Sub RemoveInvalidMailMergeFields()
 
Dim objField As Field
Dim objMMField As MailMergeFieldName
Dim blnFieldFound As Boolean
 
For Each objField In ActiveDocument.Fields
    blnFieldFound = False
    If objField.Type = wdFieldMergeField Then
        For Each objMMField In ActiveDocument.MailMerge.DataSource.FieldNames
            blnFieldFound = Trim(Mid(objField.Code, InStr(2, objField.Code, " ") + 1)) = objMMField.Name
            If blnFieldFound Then Exit For
        Next
        If Not blnFieldFound Then objField.Delete
    End If
Next
 
End Sub

Open in new window

ISDNO

ASKER

Hi irdudyk,

Thank you for your reply, however this does not solve my problem. I think my Explanation of the problem was a little unclear and I will try to clarify it here.

First of all this is not a problem regarding regular mail merge function, but instead an insert Database problem. Please see the attached PDF for an in dept explanations of the steps taken to replicate the problem.

I also need to point out that what I am looking for is to find out if there is a setting in Office 2007 or a registry setting that will solve this, or if this basically is a bug in Office 2007.
Marius Jensen / 334989

Insert-Database.pdf

ASKER CERTIFIED SOLUTION

irudyk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ISDNO

ASKER

Irudyk you are a genius!

The Macro works perfectly& Thank you!

The only thing missing is formatting from Table AutoFormat
It looks like when using the macro it ignores whatever format you choose under the Table AutoFormat button.
Its not a big issue, but if you have a solution for this as well you would really make my day!

Marius Jensen /334989

ScreenShot138.bmp

irudyk

Okay, try the following revised code:

Sub SetInsertDataField()
 
Dim d As Dialog
Set d = Dialogs(wdDialogInsertDatabase)
 
'if user did not cancel
If d.Display < 0 Then
    
    'alias for data source
    Dim a As String
    a = "a"
    
    Dim strSQL As String
    strSQL = d.SQLStatement
    strSQL = Replace(strSQL, " `", " " & a & ".`", Compare:=vbTextCompare)
    strSQL = Replace(strSQL, "(`", "(" & a & ".`", Compare:=vbTextCompare)
    strSQL = Replace(strSQL, " FROM " & a & ".", " FROM ", Compare:=vbTextCompare)
    
    Dim strFrom As String
    strFrom = Mid(strSQL, InStr(1, strSQL, " FROM `") + 6)
    strFrom = Left(strFrom, InStr(2, strFrom, "`"))
    strSQL = Replace(strSQL, strFrom, strFrom & " " & a, Compare:=vbTextCompare)
    
    'build the field instead of using Selection.Range.InsertDatabase
    'since that function doesn't seem to set the \d property of the DATABASE field
    With Selection
        .Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, PreserveFormatting:=False
        .TypeText "DATABASE \d " & Chr(34) & Replace(d.DataSource, "\", "\\") & Chr(34) & _
        " \c " & Chr(34) & Replace(d.Connection, d.DataSource, Replace(d.DataSource, "\", "\\")) & Chr(34) & _
        " \s " & Chr(34) & strSQL & Chr(34) & _
        " \l " & Chr(34) & d.Format & Chr(34) & " \b " & Chr(34) & d.Style & Chr(34) & _
        IIf(d.IncludeFields = 1, " \h", "")
        .Fields.Update
    End With
 
End If
 
End Sub

Open in new window

irudyk

Also, I found that if the length of d.Connection is too long that it will get truncated which will likely cause an error. The best best there is to only include the attributes needed within the connection. Linking to an unprotected Excel file would only require the Provider.
For other types of connections you'd have to make the connection manually (select all fields to avoid the prompting) and then view the field code details to determine what the connection string should be and manually copy and paste that into the code (unless of course someone can figure out how to retrieve the complete connection string using code).
The following revised code will pull the Provider information as make that the connection string.

Sub SetInsertDataField()
 
Dim d As Dialog
Set d = Dialogs(wdDialogInsertDatabase)
 
'if user did not cancel
If d.Display < 0 Then
    
    'alias for data source
    Dim a As String
    a = "a"
    
    'connection string (may not work in all cases depending on the data source
    Dim c As String
    c = Left(d.Connection, InStr(1, d.Connection, ";") - 1)
    
    Dim strSQL As String
    strSQL = d.SQLStatement
    strSQL = Replace(strSQL, " `", " " & a & ".`", Compare:=vbTextCompare)
    strSQL = Replace(strSQL, "(`", "(" & a & ".`", Compare:=vbTextCompare)
    strSQL = Replace(strSQL, " FROM " & a & ".", " FROM ", Compare:=vbTextCompare)
    
    Dim strFrom As String
    strFrom = Mid(strSQL, InStr(1, strSQL, " FROM `") + 6)
    strFrom = Left(strFrom, InStr(2, strFrom, "`"))
    strSQL = Replace(strSQL, strFrom, strFrom & " " & a, Compare:=vbTextCompare)
    
    'build the field instead of using Selection.Range.InsertDatabase
    'since that function doesn't seem to set the \d property of the DATABASE field
    With Selection
        .Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, PreserveFormatting:=False
        .TypeText "DATABASE \d " & Chr(34) & Replace(d.DataSource, "\", "\\") & Chr(34) & _
        " \c " & Chr(34) & c & Chr(34) & _
        " \s " & Chr(34) & strSQL & Chr(34) & _
        " \l " & Chr(34) & d.Format & Chr(34) & " \b " & Chr(34) & d.Style & Chr(34) & _
        IIf(d.IncludeFields = 1, " \h", "")
        .Fields.Update
    End With
 
End If
 
End Sub

Open in new window

ISDNO

ASKER

Thank you irudyk. That works for me :)