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

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

Sort by » oldest newest most voted

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() = "com.sun.star.drawing.GraphicObjectShape" Then
oObj.GraphicURL = ConvertToURL( strImageURL )
Exit For
End If
End If
Next i
End Function

more