• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2815
  • Last Modified:

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
0
ISDNO
Asked:
ISDNO
  • 4
  • 3
1 Solution
 
irudykCommented:
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

0
 
ISDNOAuthor 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
0
 
irudykCommented:
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

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
ISDNOAuthor 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
0
 
irudykCommented:
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

0
 
irudykCommented:
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

0
 
ISDNOAuthor Commented:
Thank you irudyk. That works for me :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now