Macro to insert an OLE object with a link to a spreadsheet table

asked 2016-09-02 11:16:01 +0200

Ulli_Wue gravatar image

updated 2016-09-02 11:16:52 +0200

Hi,

does anyone know how to build a macro which inserts an OLE link to a spreadsheet?
I know how to embed an object and how to modify it (see macro example below), but couldn't find a way to create a link to that object by macro. Basically the macro should do what is done by menue with
Insert -> Object -> OLE-Object -> Create from file (now give the filename of Calc-table) & Link to file = yes
Of course, I tried the macro recorder, without success.
Next thing I did: I was snooping through properties of an embedded object, again, without sucess.
e.g. with msgbox ThisComponent.getEmbeddedObjects().getByIndex(0).getEmbeddedObject().dbg_properties

Any ideas, comments or suggestions are highly appreciated.

Best wishes.

Ulli

sub oleexample
oDoc = ThisComponent
txt=oDoc.getText
Set TextCursor = txt.createTextCursor
Set TextEmbeddedObject = oDoc.createInstance("com.sun.star.text.TextEmbeddedObject")
Dim asize As New com.sun.star.awt.Size
asize.Height =10000
asize.Width = 10000
TextEmbeddedObject.CLSID = "47BBB4CB-CE4C-4E80-a591-42d9ae74950f"
txt.insertTextContent TextCursor, TextEmbeddedObject, False
TextEmbeddedObject.setSize asize
Set SpreadSheetDoc = TextEmbeddedObject.getEmbeddedObject
oSheets=SpreadSheetDoc.getSheets
oSheet = oSheets.getByIndex(0)
oCell = oSheet.getCellByPosition(0, 0)
oCell.setString "ABCD"
end sub

edit retag flag offensive close merge delete