Merging from SharePoint Lists in Word with Images (via Access)
Recently, we were asked to create a cleaning book that has a set of materials on objects in rooms. There were a relatively small list of fabrics and a relatively small number of objects that used those fabrics, but we had to create a cleaning plan for each room, which included instructions for every object in the room. It was all relatively straightforward – until we had to insert the images for the objects and fabrics and include fields with multiple lines of text.
We started by thinking we’d just project the columns through lookup fields. This would allow us to enter the fabric cleaning information once and have it show up multiple times. Unfortunately, you can’t project fields of some types – including multi-line columns. No problem. We can do that in Microsoft Access. We link to the external lists and join them with a query and we get all the columns that we want. That was great until we realized that the report generator won’t allow you to get images dynamically from web URLs.
That’s OK – we can use Word and do a mail merge off the Access query with all the data. Except you can’t have an image as a merge field. Ultimately, we created the mail merge with the URLs getting dumped out in the report – and I wrote a quick VBA macro/script which converts the URLs into pictures.
The script is designed for our needs – so use at your own risk. It looks for the # markers that appear that the edges of the URL. It utilizes #http:// to find the start and then extends to the next #. If the user entered a label for the URL, it will appear before that, so we expand the selection before inserting the image. (An enhancement would save the label to add as alt text.) The script also doesn’t allow any of the images to exceed 2.5″ in height – so our cleaning instructions will stay on one page.
The script is below if you need something similar.
Dim url As String
Dim pic As InlineShape
Dim maxPicHeight As Integer
maxPicHeight = 2.25 * 72 ‘ Maximum size in points – 72 per inch
Selection.GoTo wdGoToLine, wdGoToFirst, 1 ‘ Go to top of file
.Text = “#https://” ‘ Find URL start
While .Found ‘ while we found a start
Selection.Extend “#” ‘ Extend to the end of the URL
url = Mid(Selection.Text, 2, Len(Selection.Text) – 2) ‘ get URL minus bumpers
Selection.Expand wdLine ‘ Get the rest of the line
Selection.End = Selection.End – 1 ‘ Don’t take the cell/paragraph mark
Set pic = Selection.InlineShapes.AddPicture(url, False, True) ‘ Insert Shape
If (pic.Height > maxPicHeight) Then ‘ If Larger than max height then scale
pic.LockAspectRatio = True ‘ Technically unnecessary, ensure that width and height scale together
pic.ScaleHeight = maxPicHeight / pic.Height * 100 ‘ Set scale of image to get to desired height
.Execute ‘ Execute next search to see if we have more
It was amazing the number of blind paths we went down before being able to generate a report from SharePoint lists which had images, multiple lines of text, etc., and looked good when we were done.