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


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.


sub oleexample

oDoc = ThisComponent


Set TextCursor = txt.createTextCursor

Set TextEmbeddedObject = oDoc.createInstance(“”)

Dim asize As New

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


oSheet = oSheets.getByIndex(0)

oCell = oSheet.getCellByPosition(0, 0)

oCell.setString “ABCD”

end sub