Insert a photo to a Word bookmark from an Excel range, VBA

1.5k views Asked by At

I am trying to build a report generator using an excel database. I would like to insert photos into word bookmarks from an HTML link. I am wondering if it is possible to do this?

Right now my code is set up to test if I can insert the photos from the HTML link alone. Here is what it looks like:

Sub photobomb

Dim imagePath As String
imagePath ="https://dl.dropbox.com/s/uywzy5yf08apk68/130%20Boreal%20Avenuexx.jpg"

 With ActiveDocument

    .Bookmarks("imagePath1").Range
    .InlineShapes.AddPicture FileName:= "imagePath", _
    LinkToFile:=False, _
    SaveWithDocument:=False, _

End With

End Sub

The code keeps failing. Any help is greatly appreciated, thanks.

1

There are 1 answers

0
Siddharth Rout On BEST ANSWER

That is because your code is slightly incorrect. Also imagePath is a variable. When you put it within Quotes, it becomes a string. Since I don't have access to the picture link mentioned in your post, I am using a different link.

Try this (In MS Word)

Sub photobomb()
    Dim imagePath As String

    imagePath = "https://www.dropbox.com/static/images/lockbox.png"

    ActiveDocument.Bookmarks("imagePath1").Range.InlineShapes.AddPicture FileName:=imagePath
End Sub

Now all you need to do is LateBind/EarlyBind from Excel to Word and use the above code.

Here is small example which is Untested. Paste this code in an Excel module and run it

Sub Sample()
    Dim oWordApp As Object, oWordDoc As Object
    Dim FlName As String
    Dim imagePath As String

    '~~> Change link and path accordingly
    imagePath = "https://www.dropbox.com/static/images/lockbox.png"
    FlName = "C:\MyFile.Docx"

    '~~> Establish an Word application object
    On Error Resume Next
    Set oWordApp = GetObject(, "Word.Application")

    If Err.Number <> 0 Then
        Set oWordApp = CreateObject("Word.Application")
    End If
    Err.Clear
    On Error GoTo 0

    oWordApp.Visible = True

    Set oWordDoc = oWordApp.Documents.Open(FlName)

    oWordDoc.Bookmarks("imagePath1").Range.InlineShapes.AddPicture Filename:=imagePath
End Sub