Adding a named field as embedded object in Word

[JoeT97]

Hello,

I have an excel spreadsheet with multiple named fields across multiple sheets. I am generating a Word Document that is populated by embedded tables linked back to the named fields. However, I cannot find a way to add the field name to the properties of the object, so it outputs the whole sheet as the embedded object for each named field. In my commented out code, I have tried to add the Field Name as a string to the file path in the format that the field name is referenced if I was to do it manually in the field code but the program does not recognize it. Is there a good way to do this?

Function CreateBOE()

Dim strFilePath As String
strFilePath = ThisWorkbook.FullName

Set wb = ActiveWorkbook

Dim objWord
Dim objDoc
Dim objSelection

Dim ws As Worksheet
Dim nm As Name
Dim i As Integer
Dim title As String

Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Add
objWord.Visible = True

With objDoc
    For Each ws In Worksheets
        i = i + 1
        For Each nm In ws.Names
            title = nm.Name
            InsertEmbed i, title, objDoc, strFilePath
        Next

    Next
End With

End Function

Sub InsertEmbed(a, title, objDoc, strFilePath)

objDoc.InlineShapes.AddOLEObject ClassType:="Excel.Sheet.12", Filename _
    :=strFilePath, LinkToFile:=True, DisplayAsIcon:=False
'objDoc.InlineShapes.AddOLEObject ClassType:="Excel.Sheet.12", Filename _
    :=strFilePath & " " & title, LinkToFile:=True, DisplayAsIcon:=False

End Sub

Your help is much appreciated.

Thanks, Joe

 

[Macropod]

Perhaps:

Sub CreateBOE()
Dim wb As Workbook, ws As Worksheet, Nm As Name, strNm As String
Dim objWord As Object, objDoc As Object, strSrc As String

Set wb = ActiveWorkbook: Set ws = wb.ActiveSheet: strSrc = wb.FullName

Set objWord = CreateObject("Word.Application")
With objWord
  .Visible = True
  Set objDoc = .Documents.Add
End With
For Each ws In Worksheets
  For Each Nm In ws.Names
    strNm = Nm.Name
    With objDoc
      .Range.InsertAfter vbCr
      .Fields.Add .Range.Characters.Last, 56, "Excel.Sheet.12 " & Chr(34) & _
        strSrc & Chr(34) & " " & Chr(34) & strNm & Chr(34) & " \a \p", False
  Next
Next
Set ws = Nothing: Set wb = Nothing: Set objDoc = Nothing: Set objWord = Nothing
End Sub

Do note that linked objects in Word cannot span a page boundary; if you need to do that, you'll need to use a different link format.

 

[JoeT97]

Hey Macropod, thanks a lot for posting this. It set me down the right path and I got it to work with a few changes inside the For Each nm loop:

            For Each nm In ws.Names
                title = nm.Name
                title = Replace(title, "'", "")
                a = a + 1
                'InsertEmbed a, i, title, objDoc, strFilePath
                With objDoc
                    .Range.InsertAfter vbCr
                    .Fields.Add .Range.Characters.Last, 56, "Excel.Sheet.12 " & Chr(34) & _
                    strSrc & Chr(34) & " " & title & " \a \p \f 0", False
                End With
            Next

The final string looks like this:

{ LINK Excel.SheetMacroEnabled.12 "C:\\Users\\[username]\\Documents\\Proposals\\Proposal Process\\BOE_template.xlsm" BOE1!TaskDescription \a \p }

For some reason, the link did not work with the single quotes around the worksheet name (BOE1), and needed double backslashes in the file path. I don't know why but when I pasted a link from a document that was working into this one, that was what the field path ended up looking like.

Thank you,
Joe

 

[JoeT97]

My post above did not include the line that revised the strSrc code to include the double backslashes:

strSrc = Replace(strSrc, "\", "\\")

 

[Macropod]

For some reason, the link did not work with the single quotes around the worksheet name (BOE1)

It would need them if the worksheet name had spaces in it; you can get away without them otherwise.

and needed double backslashes in the file path.

Doh! I've created enough fields in my time to know about that.

Why are you still bothering with:
a = a + 1
You're not using it for anything.