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?

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

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.