Ask Your Question

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

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

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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-10 19:48:10.175846

1 Answer

Sort by » oldest newest most voted

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

librebel gravatar image

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

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 +0100 )edit

Question Tools

1 follower


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

Seen: 483 times

Last updated: Jul 20 '17