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.