Format a Linked Field

[JimmyC]

I have almost two thousand Word (2010) fields linked to an Excel worksheet in 25 separate Word files. I have linked the fields using the selection "unformatted text" so that the linked field inherits the formatting in Word. What I have learned though is that any field that has a period in it (I.e., like a full name, John G. Smith) does not format correctly. For example, if I format the field in Word to be "Bold" font, the portion of the field text prior to the period is bolded, but the portion of the text after the period is not bolded (i.e. so John G. is bold, but Smith is not bolded). All of the text in my other linked fields that do not contain a period "Bold" correctly (i.e., the entire value in the field is "bolded"). I also have fields formatted that underline the field---but I get the same result--the field characters prior to the period are properly underlined, but the characters after the period are not underlined.

I have been researching but can't seem to locate any definitive guidance---can a linked field with a period in the field value be formatted so that the entire field is in "bold" font or underlined? Thanks. JimC

[HansV]

You can add the field switch

\* charformat

to the field code to ensure that the entire field uses the same character format as the first character of the field code, i.e. of the L in LINK.

Word will add a field switch

\* mergeformat

at the drop of a hat, and this messes with the formatting, so you should remove \* mergeformat.

Here is a macro that should help. Perhaps Paul (macropod) will have a better version.

Code: Select all

Sub AddCharFormat()
    Dim fld As Field
    Application.ScreenUpdating = False
    For Each fld In ActiveDocument.Fields
        If fld.Type = wdFieldLink Then
            If InStr(1, fld.Code.Text, "mergeformat", vbTextCompare) <> 0 Then
                fld.Code.Text = Replace(fld.Code.Text, "\* mergeformat", "", , , vbTextCompare)
            End If
            If InStr(1, fld.Code.Text, "charformat", vbTextCompare) = 0 Then
                fld.Code.Text = fld.Code.Text & " \* charformat "
            End If
        End If
    Next fld
    ActiveDocument.Fields.Update
    Application.ScreenUpdating = True
End Sub

This macro removes \* mergeformat from all LINK fields, and adds \* charformat if it isn't already present.

[JimmyC]

Hans,
So does the macro "fix" all of the linked fields in the current document as you describe (i.e., replacing the \*mergeformat with the \*charformat?

If this is correct, since I am not permitted to save a Word file with macro code, I assume that I run the macro once and I then can save the word file as normal (i.e. regular word file without a macro (docx file extension) as your macro is "one and done fix" for my problem. I would then repeat this "same" process for all 27 other Word files with the links. Yes?

I have been reading about code switches for an hour via google searches but had not run across the \* charformat switch. Thank you as always--I greatly appreciate your help.
Jim

[HansV]

The code should fix all linked fields in the current document.

You could copy the code into a module in ANY Word document, for example an otherwise empty new document.

Then open one of the documents with links.
Make sure that it is the active document.
In the Visual Basic Editor, place the insertion point anywhere in the macro, then press F5 to run the macro.

[JimmyC]

Hans---thanks for your knowledge and willingness to share it at the user's level...in my case, you always patiently explain the topic so I learn something with every interaction in this wonderful lounge/forum. Again, I am grateful. Thank you. Jim

[JimmyC]

Hans,
I do have a follow-up question.

I am probably only 30% done with this project and will be creating additional Word docs with links to the Excel document. Can I do something "different" when I create the link and format the linked field in Word? Or is the only "cure" to link the field as I have been doing as unformatted text, format the linked field in Word and then run your macro when I have linked all of the fields in the newly created Word document? Thank you. Jim

[HansV]

After creating a link, you can do the following:
- Right-click the link and select Toggle Field Codes from the context menu.
- If you see \* MERGEFORMAT, remove it.
- Add \* CHARFORMAT just before the closing field bracket }
- Format only the L in LINK at the beginning of the field the way you want (bold, italic, green, underlined, 24 points, whatever).
- Press F9 to hide the field code and to update the field.

[JimmyC]

Hans---thank you very much. I will use your approach as I create the links. Wow, I never would have found this information in my lifetime without your help. Jim

[macropod]

If you add the following code to a document, it will apply the Charformat switch to all LINK fields in all documents in a selected folder.

Code: Select all

Sub UpdateDocuments()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, strDocNm As String, wdDoc As Document, Fld As Field
strDocNm = ActiveDocument.FullName
strFolder = GetFolder
If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.doc", vbNormal)
While strFile <> ""
  If strFolder & "\" & strFile <> strDocNm Then
    Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
    With wdDoc
      For Each Fld In .Fields
        With Fld
          If .Type = wdFieldLink Then
            If InStr(.Code.Text, "MERGEFORMAT") > 0 Then
              .Code.Text = Trim(Replace(.Code.Text, "\* MERGEFORMAT", ""))
            End If
            If InStr(.Code.Text, "CHARFORMAT") = 0 Then
                .Code.Text = Trim(.Code.Text) & " \* CHARFORMAT"
            End If
          End If
        End With
      Next Fld
      .Fields.Update
      .Close SaveChanges:=True
    End With
  End If
  strFile = Dir()
Wend
Set wdDoc = 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

You don't even need to save the document you add the code to, so you could add it to a document you discard after running the macro on whatever folder(s) you want to process. The macro will process .doc, .docx and .docm files alike.

[JimmyC]

Paul,
Thank you. Please excuse my tardy reply as I have been away from the lounge for a few days moving my daughter to her college dorm room. Too bad, you couldn't write a macro to make that process go faster. Again, thank you as I will resume work on this project today. JimC