?
Solved

Word 2007: Invalid Merge field

Posted on 2009-02-18
7
Medium Priority
?
2,677 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
0
Comment
Question by:ISDNO
  • 4
  • 3
7 Comments
 
LVL 23

Expert Comment

by:irudyk
ID: 23673342
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
 

Author Comment

by:ISDNO
ID: 23679163
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
 
LVL 23

Accepted Solution

by:
irudyk earned 1000 total points
ID: 23681879
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ISDNO
ID: 23683750
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
 
LVL 23

Expert Comment

by:irudyk
ID: 23683964
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
 
LVL 23

Expert Comment

by:irudyk
ID: 23684159
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
 

Author Comment

by:ISDNO
ID: 23689535
Thank you irudyk. That works for me :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question