Adding a named field as embedded object in Word

JoeT97

New Member
Joined
Oct 5, 2017
Messages
3
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?

Code:
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Perhaps:
Code:
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.
 
Last edited:
Upvote 0
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:

Code:
            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
 
Upvote 0
My post above did not include the line that revised the strSrc code to include the double backslashes:

Code:
strSrc = Replace(strSrc, "\", "\\")
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top