We help IT Professionals succeed at work.

Word 2007: Invalid Merge field

ISDNO
ISDNO asked
on
Medium Priority
3,109 Views
Last Modified: 2012-05-06
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2008

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2008
Commented:
Okay, I see what you mean now.  After you insert the database field and either click the OK or Remove Field button for each Invalid Merge Field prompt, try the following to add an alias name to the fields and sheet source you are picking your data from, So:
  • Right-click the database field and select Toggle Field Codes
  • At or near the end of the field you should see a \s switch
       from your example it would be something like
      \s "SELECT `Name`, `Phone` FROM `Sheet1$`"
  • Change the \s switch so that it appears as
       \s "SELECT a.`Name`, a.`Phone` FROM `Sheet1$` a"
    You can change the a alias to something else if you prefer
  • Press the F9 key to update the field and you should no longer get the prompts
Having outlined the above, I realize that this doesn't help you too much when, as you've said, there are a "few hundred columns/fields to exclude/remove". So, try the following code to insert the database field.  Hope that helps!

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) & IIf(d.IncludeFields = 1, " \h", "")
        .Fields.Update
    End With
 
End If
 
End Sub

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2008

Commented:
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

CERTIFIED EXPERT
Top Expert 2008

Commented:
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

Author

Commented:
Thank you irudyk. That works for me :)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.