libreoffice-calclibreoffice-basic

LibreOffice Calc Macros - how to select/refer/access to an image in a sheet


Hy guys, I'm quite a newbie to LibreOffice. That's why I ask your help: basically I had inserted an image (.png) in my Calc sheet, image's name is "timber". I'd like to reposition it at some condition. The problem is that I do not find a way to select/refer/access to it by name, in order to get it moved. Any advice? Thank you in advance.

This was my attempt:

Sub moveImage
Dim oSel As Object
Dim oItem As Object
Dim aPosition As New com.sun.star.awt.Point

REM losing my religion!#?!

oSel = ThisComponent.getCurrentSelection()
If oSel.ImplementationName = "com.sun.star.drawing.SvxShapeCollection" Then
    oItem = oSel.getByIndex(0)
    aPosition.Y = 2200
    oItem.setPosition(aPosition)
Else
    MsgBox "Image not selected"
End If
End Sub

I guess I should add something at REM place...


Solution

  • Yes, you can't get an object by name, only .getByIndex(). But you can loop through all the items in your spreadsheet and check the name of each one:

    Sub moveImage2
    Dim oDrawPages As Variant, oDrawPage As Variant, oItem As Variant
    Dim i As long, j As Long 
    Dim aPosition As New com.sun.star.awt.Point
        oDrawPages = ThisComponent.getDrawPages()
        For i = 0 To oDrawPages.getCount()-1
            oDrawPage = oDrawPages.getByIndex(i)
            For j = 0 To oDrawPage.getCount()-1
                oItem = oDrawPage.getByIndex(j)
                If oItem.getName() = "timber" Then
                    aPosition = oItem.getPosition()
                    aPosition.X = 2200
                    aPosition.Y = 2200
                    oItem.setPosition(aPosition)
    '  You can do Exit Sub now - but what about other images wih name "timber"? It may be several!
                EndIf 
            Next j
        Next i
    End Sub