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

edit retag close merge delete

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

Any way around this to your knowledge?

Thank you.

( 2016-11-07 17:26:01 +0200 )edit

Sort by » oldest newest most voted

@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
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
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.Sheet = 0
oCA.Column = 0
oCA.Row = 0
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.

more

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 ...

( 2016-09-10 11:41:43 +0200 )edit

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.

( 2016-09-10 17:12:37 +0200 )edit

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

Any way around this to your knowledge?

Thank you.

( 2016-11-07 17:28:32 +0200 )edit

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

( 2016-11-07 19:27:16 +0200 )edit

@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.

( 2016-11-19 21:32:12 +0200 )edit

@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.

( 2016-11-20 00:28:47 +0200 )edit

@Ratslinger - Ok will do then. Thank you.

( 2016-11-20 21:53:36 +0200 )edit