Ask Your Question
1

Create macro to insert and link a OLE Spreadsheet?

asked 2016-09-07 11:39:06 +0100

Ulli_Wue gravatar image

updated 2016-09-10 04:29:51 +0100

Ratslinger gravatar image

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 flag offensive close merge delete

Comments

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.

monosij gravatar imagemonosij ( 2016-11-07 17:26:01 +0100 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2016-09-10 04:14:14 +0100

Ratslinger gravatar image

updated 2017-12-11 07:15:42 +0100

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

edit flag offensive delete link more

Comments

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

Ulli_Wue gravatar imageUlli_Wue ( 2016-09-10 11:41:43 +0100 )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.

Ratslinger gravatar imageRatslinger ( 2016-09-10 17:12:37 +0100 )edit

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

monosij gravatar imagemonosij ( 2016-11-07 17:28:32 +0100 )edit

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

Ratslinger gravatar imageRatslinger ( 2016-11-07 19:27:16 +0100 )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.

monosij gravatar imagemonosij ( 2016-11-19 21:32:12 +0100 )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.

Ratslinger gravatar imageRatslinger ( 2016-11-20 00:28:47 +0100 )edit

@Ratslinger - Ok will do then. Thank you.

monosij gravatar imagemonosij ( 2016-11-20 21:53:36 +0100 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2016-09-07 11:39:06 +0100

Seen: 218 times

Last updated: 2 days ago