Consulting

Results 1 to 7 of 7

Thread: Insert word doc at word bookmark from excel cell reference

  1. #1
    VBAX Regular
    Joined
    May 2018
    Posts
    10
    Location

    Insert word doc at word bookmark from excel cell reference

    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!!

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    VBAX Regular
    Joined
    May 2018
    Posts
    10
    Location
    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!

    Last edited by macropod; 06-28-2018 at 06:40 PM. Reason: Added code tags

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    VBAX Regular
    Joined
    May 2018
    Posts
    10
    Location
    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.

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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, "\")))
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  7. #7
    VBAX Regular
    Joined
    May 2018
    Posts
    10
    Location
    Legend! Perfect, thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •