Create macro to insert and link a OLE Spreadsheet?

[Ulli_Wue]

Hi,

does anyone know how to build a macro that basically does this (see menue)


Insert → Object → OLE-Object → Create from file (e.g. filename of Calc-table) & Link to file = yes


I tried the macro recorder, however, without any success and searched for macro snippets in a lot of places. I would like to insert a large amount of tables into a document and create a link to those tables, later update those links in case of changes.

Any ideas?

Ulli

[monosij]

If you were able to solve this would be great to know. I am trying a similar thing.

Also when updating a linked spreadheet, when rows are added, updating the link in Writer does not add the rows. The entire DDE link needs to deleted and recreated with the new added rows.

Any way around this to your knowledge?

Thank you.

[Ratslinger]

@Ulli_Wue I don’t have a complete answer for you but a partial. Here is the the code to insert an Ole object of type spreadsheet into a writer document:

sub oleexample
oDoc = ThisComponent
txt=oDoc.getText
obj = oDoc.createInstance("com.sun.star.text.TextEmbeddedObject")
obj.CLSID = "47BBB4CB-CE4C-4E80-a591-42d9ae74950f"
obj.attach(ThisComponent.currentController().Selection.getByIndex(0))
oXEO = obj.ExtendedControlOverEmbeddedObject
oXEO.doVerb(0)
end sub    

This inserts the spreadsheet at the current cursor location. Like you, I couldn’t find much on the subject. I had absolutely no luck with finding a method for the link (even using Mri ran into a lot of brick walls). Didn’t want to waste the code so I’m posting it to give you another step forward. If I do find anything on the “link” function I will post back.

Edit 12/10/2017:

It has been a while but I been able to apply a link to the spreadsheet created above. This is NOT an OLE object as requested but can be made to appear the nearly the same. The major difference is all sheets must be set individually in code and you cannot update the source from this object. Here is the revised code:

Option Explicit
Sub LinkedSheet
    Dim oDoc				 as Object
    Dim oEmbeddedObject		 as Object
    Dim oEmbeddedObjects	 as Object
    Dim oEmbeddedSheet		 as Object
    Dim obj					 as Object
    Dim oXEO				 as Object
    Dim oEOsheet			 as Object
    Dim oCA					 as Object
    Dim oAreaLinks			 as Object
    Dim oSheetLink			 as Object
    oDoc = ThisComponent
    oEmbeddedObjects = ThisComponent.getEmbeddedObjects()
    If oEmbeddedObjects.hasByName("mySheet") Then
        oEmbeddedSheet = oEmbeddedObjects.getByName("mySheet")
        oEmbeddedSheet.dispose()
    EndIf
    obj = oDoc.createInstance("com.sun.star.text.TextEmbeddedObject")
    obj.CLSID = "47BBB4CB-CE4C-4E80-a591-42d9ae74950f"
    obj.Name = "mySheet"
    obj.attach(ThisComponent.currentController().Selection.getByIndex(0))
    oXEO = obj.ExtendedControlOverEmbeddedObject
    oXEO.doVerb(0)
    oXEO.changeState(3)
    oEmbeddedObjects = ThisComponent.getEmbeddedObjects()
    oEOsheet = oEmbeddedObjects.getByName("mySheet")
    oEmbeddedObject = oEOsheet.getEmbeddedObject()
    oCA = createUnoStruct("com.sun.star.table.CellAddress")
    oCA.Sheet = 0
    oCA.Column = 0
    oCA.Row = 0
oEmbeddedObject.AreaLinks.insertAtPosition(oCA,"file:///home/YOUR_DIRECTORY/YOUR_SHEET.ods","AREAorRANGE","calc8", "")
    oAreaLinks = oEmbeddedObject.AreaLinks
    oSheetLink = oAreaLinks.getByIndex(0)
    oSheetLink.RefreshPeriod = 75
End Sub

The embedded spreadsheet is given a name (‘mySheet’ used here) and checked early if exists. If so the old one is deleted. Once the sheet object is inserted the link is established in ‘AreaLinks’. To create, use the URL for the spreadsheet and area or RangeName to be linked. The final step gets that link (Index is used but by name available) and sets the refresh period (in seconds). So every X seconds the data will be updated from the SAVED original.

[Ulli_Wue]

Thank you for your answer, this took me a huge step forward, still searching how to manipulate or create a link using dbg_properties and / or dbg_methods …

[Ratslinger]

If you do find the answer to the link, please post to satisfy my curiosity (not a need) and for others who may also want to know.

[monosij]

@Ratslinger and @Ulli_Wue If you were able to solve this would be great to know. I am trying a similar thing.

Also when updating a linked spreadsheet, when rows are added, updating the link in Writer does not add the rows. The entire DDE link needs to deleted and recreated with the new added rows.

Any way around this to your knowledge?

Thank you.

[Ratslinger]

@monosij - have not resolved nor looked at recently. As for rows added, see no problem on my system. Maybe open as new question.

[monosij]

@Ratslinger - Thank you for your answer. Sorry took some time to respond. Just seeing it. So when you add rows to a linked spreadsheet, on the spreadsheet Calc file, then come to Writer doc to update the rows in the DDE Link - new rows are added / extended on the Writer doc?

It does NOT happen for me on Ubuntu. I have to delete the link and recreate the link with the new set of rows. I can update the other links however with the now updated column ranges as long as the total rows do not change.

[Ratslinger]

@monosij - I believe you are not understanding the topic of this question. This is dealing with linked OLE Objects and not linked cells. With linked objects, the entire spreadsheet document is linked and not just cells.

[monosij]

@Ratslinger - Ok will do then. Thank you.