Image injection

Office 2016

I'm making a report in Word and importing most of the data from Excel. At the end, the client wants images and I don't want to insert a dozen for every report. Each report is printed to a PDF, if that makes a difference.

This is for testing parts. I have a Workbook for recording all the data points. One Sheet has a dropdown to select the part ID and displays all the needed data for that part and does some conditional formatting to check for out of spec and typos (like not over 100%, etc).

From Word, I linked the tables that I need for the report - most of those are working well. The issue I have is that the client is asking for a dozen images at the end of the report. I can figure out how to generate the file path to the images for that part ID in excel. Is there a way to use that to inject the images into Word? It would be 2 images per page with a caption under each image.

My focus has been mostly Excel, not so much Word.

Thanks for any guidance.

Certainly VBA can grab image and embed in document https://stackoverflow.com/questions/...ument-with-vba. I have no idea how you would trigger this procedure from Word.

Have you considered migrating to a relational database such as Access and use its Report object for data output? It is simple to dynamically integrate images into report.

You could use a macro to add the images. I assume that you are running the process from Excel, in which case you need something like the following to create a table at the end of the current document with two rows for each image listed in your worksheet. Enter the total number of images to include where indicated and the cell ranges that contain the image paths and caption texts as appropriate

Code:

---

Sub Macro1()
Dim wdApp As Object
Dim oDoc As Object
Dim oRng As Object
Dim oTable As Object
Dim oCell As Object
Dim i As Long, j As Long
Dim oRow As Object

    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err Then 'This part should not be necessary if the document is already open in Word.
        Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0

    Set oDoc = wdApp.ActiveDocument

    Set oRng = oDoc.Range
    oRng.Collapse 0
    oRng.InsertParagraphBefore
    Set oTable = oDoc.Tables.Add(Range:=oRng, _
                                          NumRows:=2, _
                                          NumColumns:=1, _
                                          DefaultTableBehavior:=1, _
                                          AutoFitBehavior:=0)
    Set oRow = oTable.Rows(1)
    Set oCell = oRow.Cells(1).Range
    oCell.End = oCell.End - 1
    oCell.Text = "Image 1" 'Replace from Excel
    'oCell.InlineShapes.AddPicture _
    FileName:="path of first image from Excel", _
    LinkToFile:=False, _
    SaveWithDocument:=True

    Set oRow = oTable.Rows(2)
    Set oCell = oRow.Cells(1).Range
    oCell.End = oCell.End - 1
   
    oCell.Text = "Caption 1" 'Replace from Excel

    For i = 2 To 12    '12 being the number of images from Excel
        If i Mod 1 = 0 Then
            oTable.Rows.Add
            oTable.Rows.Add
        End If
        j = oTable.Rows.Count
        Set oRow = oTable.Rows(j - 1)
        Set oCell = oRow.Cells(1).Range
        oCell.End = oCell.End - 1
       
        oCell.Text = "Image " & i 'Replace from Excel
        'oCell.InlineShapes.AddPicture _
        FileName:="path of next image from Excel", _
        LinkToFile:=False, _
        SaveWithDocument:=True

        Set oRow = oTable.Rows(j)
        Set oCell = oRow.Cells(1).Range
        oCell.End = oCell.End - 1
       
        oCell.Text = "Caption " & i 'Replace from Excel
    Next i
End Sub

---

Great, this method works really well and efficiently for adding images using a macro.

Quote:

---

Originally Posted by June7

Certainly VBA can grab image and embed in document https://stackoverflow.com/questions/...ument-with-vba. I have no idea how you would trigger this procedure from Word.

Have you considered migrating to a relational database such as Access and use its Report object for data output? It is simple to dynamically integrate images into report.

---

I experienced the same issue, so I appreciate it. When it comes to adding images using macros, this strategy is really effective.

Just a quick question here. I noticed that the additional code to get the last date modified is written as

Code:

---

Dim fs = Object
Set fs = CreateObject("Scripting.FileSystemObject") debug.Print fs.GetFile(.SelectedItems(i)).DateLastModified

---

Is the first line a typo or is it acceptable to write Dim fs = Object as apposed to Dim fs As Object?

Aussie, where do you see that code? Did you post in correct thread?

Follow the link to Stackoverflow

Looks like typo to me.

I am a spammer and I like to spam! I am no longer a member of this forum...