Excel to Word: Manipulating Field Codes and switches

Question

I am building a Userform (In Excel) that gives the user the following options on Excel to Word automation:

All these options are inspired by this Microsoft article.

• Update links automatically / manually / break link (\a switch)
• Store graphical data (\d switch)
• Insert as bitmap (\b switch)
• Insert as picture (\p switch)
• Maintain formatting of source file (\f 4 switch)
• Match the formatting of the destination document (\f 5 switch)
• Insert as HTML (\h switch)
• Insert as RTF (\r switch)
• Insert in text-only format (\t switch)
• Insert as Unicode text (\u switch)
• Preserve formatting after update (* MERGEFORMAT switch)

I know I can record a Macro to send each option to Word and then interpret the results, but does anyone know how I can control the syntax sent (maybe as a built string) to Word instead of heavy coding and research for each option above?

Also, does anyone know which options cannot be used with which? For example I can't send as bitmap and as a picture.

The syntax I want to send is:

{ LINK ClassName "FileName" [PlaceReference ] [Switches ] }

For example:

{ LINK Excel.Sheet.8 "C:\\My Documents\\Profits.xls" "Sheet1!R1C1:R4C4" \a \p }

Any advice or assistance would be appreciated.

Thanks.

R

Answer 1

Yes, you can use a "built string". It's probably the simplest way to do it.

The safest way to insert a field in Word is to insert an "empty" field, i.e. one that has type wdFieldEmpty rather than the specific type wdFieldLink, but provide the complete text of the field. Then update it (I have no memory for details so I tend to experiment to see if that is necessary of not).

e.g., assuming you have a reference to a Word.Range object called rng in the word document, and you have a reference to the Word object library, you might use something like this:

Dim fld As Word.Field
Dim fldText As String
fldText = "LINK Excel.Sheet.12 ""C:\\test\\xl sources\\Book1.xlsx"" ""Sheet1!R3C1:R5C3"" \a \f 4 \r"
 ' "False" = don't insert \*Mergeformat
Set fld = rng.Fields.Add(rng, Word.wdFieldType.wdFieldEmpty, fldtext, False)
fld.Update

If you aren't referencing the Word object library, you could use

Dim fld As Object
Dim fldText As String
fldText = "LINK Excel.Sheet.12 ""C:\\test\\xl sources\\Book1.xlsx"" ""Sheet1!R3C1:R5C3"" \a \f 4 \r"    
Set fld = rng.Fields.Add(rng, -1, fldtext, False)
fld.Update

Some things to consider:

• in the .docx format, only some Paste Special formats will insert a field code into the document. Others will insert a linked object. So inserting a { LINK } field is not necessarily going to have exactly the same effect as a user who does Paste Special
• the COM ProgId (Excel.Sheet.12 does not need to be quoted, but you do need to use the correct ProgId
• The path/file name may need to be quoted if it contains special characters such as space, and will need to have backslashes doubled up
• The "subset" (in my example, Sheet1!R3C1:R5C3 should either be sheetname!RangeInRCFormat or rangename. You may need sheetname!rangename for a range that has sheet scope rather than workbook scope and is not in the first sheet.
• You cannot guarantee that whatever string you provide to Word will survive a field update. Word may replace the LINK code with its own representation of the link. In fact, it is arguably a bonus that Word interprets COM LINK strings in the way you might hope.

As for the "what you can use with what"

\a switch - it's either \a or not \a. You can't insert *and* break the link using the field

\d switch - Off the top of my head, I'm not sure what \d does these days. You probably can't avoid storing graphical data in .docx

\b, \p (basically a Word wmf/emf format file, \h, \r, \t, \u are mutually exclusive

\f - you can only have one \f option ( e.g. your 4 or 5)

\*Mergeformat tries to preserve the formatting used when you last inserted, and/or that you applied after that. But for complex formatting such as a table layout, you will need to discover whether it does what you need or not.

If you need to update an existing LINK field in Word, you as long as you can locate the field, e.g you know it's the 3rs field in the document, you should be able to replace its field code and update the field again, e.g.

'Let's say the variable doc contains a reference to the Word Document
Dim newFldText As String    
newFldText = "LINK Excel.Sheet.12 ""C:\\test\\xl sources\\Book1.xlsx"" ""Sheet1!R3C1:R5C3"" \a \f 4 \r \*MergeFormat" 
With doc.Fields(3)
  .Code.Text = newFldText
  .Update
End With