Image injection

[Greymud]

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.

[June7]

Certainly VBA can grab image and embed in document https://stackoverflow.com/questions/58643827/inserting-images-into-a-word-document-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.

[gmayor]

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


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

[tonyadams]

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

[hueljannie]

Certainly VBA can grab image and embed in document https://stackoverflow.com/questions/58643827/inserting-images-into-a-word-document-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.

[Aussiebear]

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


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?

[June7]

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

[Aussiebear]

Follow the link to Stackoverflow

[June7]

Looks like typo to me.