Ask Your Question

In Calc, is there a way to set the source of an image using a formula

asked 2017-07-20 09:20:26 +0200

Jaguar gravatar image

I have a spreadsheet that uses imported data. One of the fields is the URL of an image. I would like an image in the spreadsheet to use that URL as its source. The URL is obtained using VLOOKUP, so I'd like to be able to use the formula I have to get the URL to set the image's source. Is there any way to use my formula to set the image?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-07-20 14:08:25 +0200

librebel gravatar image

updated 2017-07-20 17:33:45 +0200

Hello @Jaguar

if i interpreted your meaning correctly, then you could use the following function setImageURL() for that:

Supposed that you inserted an Image object ( via the menu "Insert : Image..." ) into your sheet and named it "Image 1", and supposed that your image URL is stored inside cell A1, then you could enter the following formula into a dummy cell ( e.g. cell F1 , this cell will remain empty except when an error occurs ),

=SETIMAGEURL( A1; "Image 1" ) ( NB. the semicolon might be different in your locale ...)

Then whenever the value in cell A1 changes to a valid image URL, the graphic in Image1 is automatically updated.

Function setImageURL( strImageURL As String, strImageObjectName As String )
REM This changes the graphic of a named image <strImageObjectName> to the specified <strImageURL>.
REM For example if you inserted an Image called "Image 1" into your sheet, then you can set its graphic by calling:
REM     setImageURL( "/home/user/Pictures/my_picture.jpg", "Image 1" )
REM Or as a Calc formula:     =SETIMAGEURL( A1; "Image 1" )
    Dim oDrawPage as Object, oObj As Object,i As Integer
    On Local Error Resume Next
    oDrawPage = ThisComponent.getDrawPages().getByIndex(0)  'ThisComponent.DrawPage     REM for Base, Writer
    For i = 0 To oDrawPage.getCount() - 1
        oObj = oDrawPage.getByIndex( i )
        If oObj.getName() = strImageObjectName Then     REM Found.
            If oObj.getShapeType() = "" Then
                oObj.GraphicURL = ConvertToURL( strImageURL )
                Exit For
            End If
        End If
    Next i
End Function
edit flag offensive delete link more


So, there's no internal way and you need to write a custom function; that's cool. I actually created a similar function while waiting to see if there was an internal function; same signature and functionally similar code. I guess, looking at the infrastructure, it wouldn't make sense to include such an obscure function. Thanks.

Jaguar gravatar imageJaguar ( 2017-07-21 04:12:19 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-07-20 09:20:26 +0200

Seen: 137 times

Last updated: Jul 20 '17