embed pictures from web by url in excel spreadsheet (or oo calc)

Question

I want to embed a picture from the web in an excel spreadsheet.

I want to embed the picture by url, in the same manner that pictures are embedded in an html page. I don't want to download the picture and insert it as a regular picture.

And I want the picture to be displayed (if I am online, of course), not just the url.

Can this be done? If so how?

Preferably in excel, but OO calc would be ok to

Solution 1

In Excel, go to Insert > Picture > From file... and type the URL of the picture you want to insert. I don't know if the picture updates itself since I've never tried it.

Solution 2

This is my modified version of the answer shared by tomelin5. It works in Excel 2016 and likely in (much) earlier versions too.

In my case I created a Microsoft PowerApps app to collect and store signatures using the pen input control.

Aside: In case you're reading this because you're trying to create a PowerApp: the magic sauce to save the contents of your controls to the spread sheet is the Patch function.

The way it works is that the pen input control saves images as PNG files in a directory and stores relative addresses to the PNG files as URLs in cells in a spread sheet: URL e.g. .\MyAppName_images\x829ca33re2d6114588e59ca45829d21.png

I wanted to display those signatures in that Excel spreadsheet so they could be sorted using the other data entered through through the app. tomelin5's solution worked so nicely as a basis for my solution I figured I ought to share my remix.

My solution stores the URLs in column "A" (1) and places the images themselves in column "I" (9). It also adjusts the height of the rows to the column width of column 9, though, you will likely want to modify/eliminate that behaviour.

All URLs are processed beginning with A2 and extending to the last filled cell in column A. Note that my solution does not do any exception handling and you'd need that in case images were unavailable.

Sub Button1_Click()
    ' https://msdn.microsoft.com/en-us/library/office/aa221353(v=office.11).aspx
    ' http://www.excelhowto.com/macros/loop-range-cells/
    ' https://www.excelcampus.com/vba/find-last-row-column-cell/
    ' https://superuser.com/questions/52760/embed-pictures-from-web-by-url-in-excel-spreadsheet-or-oo-calc#

    Dim Pic As Picture
    Dim SrcRange As Range
    Dim LastRowA As Long

    LastRowA = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row

    Set SrcRange = ActiveSheet.Range(Cells(2, 1), Cells(LastRowA, 1))

    SrcRange.Rows().RowHeight = ActiveSheet.Columns(9).Width

    For Each Cell In SrcRange.Cells
        With Cell
            Set Pic = .Parent.Pictures.Insert(.Value)
            With .Offset(, 8)
                Pic.Top = .Top
                Pic.Left = .Left
                Pic.Height = .Height
                Pic.Width = .Width
                Pic.Border.Color = vbRed
            End With
        End With
    Next
End Sub

Solution 3

If the URL is in C1, this will put the image in B1 (tested in Excel 2003 and 2013 by me):

Sub Test()
    Dim Pic As Picture
    Application.ScreenUpdating = False
    With ActiveSheet.Range("C1")
        Set Pic = .Parent.Pictures.Insert(.Value)
        With .Offset(, -1)
            Pic.Top = .Top
            Pic.Left = .Left
            Pic.Height = .Height
            Pic.Width = .Width
        End With
    End With
    Application.ScreenUpdating = True
End Sub

source

Comments

[Arjan]

Just in case this ever gets you strange errors, then it's good to know that Office might use a browser ("User Agent") known as "Microsoft Office Existence Discovery" before even trying to fetch the image. That User Agent might be seen as a search robot, and hence on some web sites might have different access rights than the IE component that is used to actually fetch the image... A little more info at superuser.com/questions/41935/…

[harrymc]

I believe that will just read the picture from the web.

[ukanth]

Yes, It does. It works

[Dan]

I think the point is to do it programatically.

[alex]

@Dan not really, otherwise it would have been on stackoverflow.