Insert word doc at word bookmark from excel cell reference

[JayRay]

Hi All,

In Excel I have 15 columns which each contain dynamic lists that show filepaths to word documents. For example, Column 8 may have 8 cells each containing a filepath, column B may have 3 cells containing file paths etc...The number of cells in each column is going to be variable - i.e. column A might have 8 entries today, but 9 tomorrow.

I would like to create a VBA script in Excel to take each of the filepaths and paste the contents at a specified bookmark in word. Each of the 8 filepaths in column A would paste to 'bookmark1', Each of the three filepaths in column B would paste to 'bookmark2'.

If it helps, in the Excel sheet the first filepath entry in each column is in row 8.

Any help would be greatly appreciated!!

[macropod]

You might try something like:

Sub InsertDocsAtBookmarks()
Dim ws As Worksheet, objWord As Object, strBkMk
Dim r As Long, c As Long, lRow As Long, strFl As String
Set ws = ActiveWorkbook.Sheets(1)
lRow = ws.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
Set objWord = CreateObject("Word.Application")
With objWord
.Visible = True
.Documents.Open "S:\xxxxxx.docm", , False, False
With .ActiveDocument
For c = 2 To 16
For r = lRow To 8 Step -1
strFl = ws.Cells(r, c).Value: strBkMk = "bookmark" & c - 1
If strFl <> "" Then
If Dir(strFl) <> "" Then
If .Bookmarks.Exists(strBkMk) Then
.Bookmarks(strBkMk).Range.InsertFile strFl
End If
End If
End If
Next
Next
End With
End With
End Sub

[JayRay]

Fantastic, thank you very much! I've ended up with the following, which seems to be working well:


Private Sub CommandButton2_Click()
Dim wb As Workbook
Dim ws As Worksheet, objWord As Object, strBkMk
Dim r As Long, c As Long, lRow As Long, strFl As String
Set wb = ActiveWorkbook
Set ws = Sheets("xxxxxxxxx")
lRow = ws.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
Set objWord = CreateObject("Word.Application") With objWord
Visible = True
objWord.Documents.Open (https://protect-au.mimecast.com/s/BkMQCk8vzVfXzn4Nu2L6GN?domain=objword.documents.open) ws.Range("B2").Value
With .ActiveDocument
For c = 1 To 16
For r = lRow To 8 Step -1
strFl = ws.Cells(r, c).Value: strBkMk = "bookmark" & c
If strFl <> "" Then
If Dir(strFl) <> "" Then
If .Bookmarks.Exists(strBkMk) Then
.Bookmarks(strBkMk).Range.InsertFile strFl
End If
End If
End If
Next
Next
End With
End With
End Sub


The only downside is that it is automatically saving the document that it is pasting in to. I feel that this is a super basic thing to change; but my searches have come up blank.

Also, as the file becomes larger I feel that I'll need to move to embedded attachments rather than inserted files. I'm assuming that would be a small change of some description at this line?:

.Bookmarks(strBkMk).Range.InsertFile strFl

Thank you again for such a great and quick response - I really appreciate it. Apologies also for such basic follow up questions!

[macropod]

There is nothing in the code I posted or in yours that triggers a save; if that's happening it must be due to some other process you're running.

I'm not sure what you mean about

move to embedded attachments rather than inserted files
You can insert as icons, but the file size will be the same. The other option is to insert as links, which can be used to reduce file size, but that creates problems if you need to move anything other than the file containing the links to another folder; it's also likely to degrade performance.

[JayRay]

Thanks again for the reply. I have had some issues with word today, so it is likely that the file saving issue is related to that. Hopefully that fixes itself in due course!

As far as the other statement - I did mean inserting as icons. Whilst the file size will remain the same, the number of documents being inserted will end up making the master file unwieldy at some point. i.e. there is potential there for around 90 documents/360 pages to be added to the document. If I can display the inserted files as icons it would make thing visually easier.

Thanks again - I'm learning a lot from your posts throughout the forum - really appreciate what you do.

[macropod]

To do that, you'd change:
.Bookmarks(strBkMk).Range.InsertFile strFl
to:

.Bookmarks(strBkMk).Range.InlineShapes.AddOLEObject ClassType:="Word.Document.12", _
FileName:=strFl, LinkToFile:=False, DisplayAsIcon:=True, IconFileName:=Application.Path & _
"\WINWORD.EXE", IconIndex:=1, IconLabel:=Split(strFl, "\")(UBound(Split(strFl, "\")))

[JayRay]

Legend! Perfect, thank you!