Identifying DDE-linked tables

From The Document Foundation Wiki
< Macros‎ | Writer
Jump to: navigation, search
Other languages:
English • ‎français • ‎italiano


The menu Edit ▸ Links allows you to recognize the links present in a document, including:

  • File source (URL)
  • Element (range)
  • Type (soffice)
  • Refresh (automatic/manual)

On the other hand, neither this approach, nor the navigator allows you to know which are the tables concerned (name or position in the document).

The purpose of the below macro is to locate tables created by DDE links.[1]

The macro inserts in the first cell of a table a comment consisting of:

  • source (URL of a Calc spreadsheet for example),
  • the range used in the source.

(More on comment manipulation with macro in Macros/Writer/001.)

Note that the collection TextFieldMasters would only retrieve the information displayed by the Edit ▸ Links menu and TextTables does not give us any more information.

The DDE connection of table data is contained in an element <office:dde-source>[2].

We can therefore browse the file content.xml to search for such elements. The program starts by checking that the current document has this file and then it proceeds to read it.

For this we will make use of the service[3] associated with the listner[4].


In LibreOffice Basic:

Option Explicit

' This library is Copyright (C) 2012 Pierre-Yves Samyn, except otherwise indicated
' Purpose: Frame the selection or create an empty frame if no selection

' This program is free software: you can redistribute it and/or modify
' it under the terms of the GNU General Public License as published by
' the Free Software Foundation, either version 3 of the License, or
' (at your option) any later version.
' This program is distributed in the hope that it will be useful,
' but WITHOUT ANY WARRANTY; without even the implied warranty of
' GNU General Public License for more details.
' You should have received a copy of the GNU General Public License
' along with this program.  If not, see <>.
Dim oDoc As Object
Dim oDocHandler As Object
Dim oTableName As String

' Description of macro:

' Context    :  for LibreOffice Writer odt document
' Objective  :  identify the DDE-linked tables
' Function   :  insert a comment of the source (URL of spreadsheet for ex.), and the range

' Info about usage of UNO's XML sax parser via the API:

Sub Comment_DDE_Table()

    Dim oContent As Object
    Dim oFlux As Object
    Dim oSaxParser As Object
    Dim oDocEventsHandler As Object
    Dim oInputSource As Object

    oDoc = ThisComponent

    ' Only perform macro if document is a TextDocument
    If oDoc.supportsService("") Or _
        oDoc.supportsService("") Then

        ' We want to read the content.xml file, so we check that ThisComponent makes reference to a object with such a file
        If oDoc.DocumentStorage.HasByName("content.xml") Then
            ' Test if the document has been modified:  we will read the content.xml file hence we
            ' ask for saving, to be sure to work on the most recent content
            If oDoc.ismodified Then
                MsgBox "The document must be saved to latest version! (Ctrl+S)", 64, "Comment_DDE_Table()"
                oContent = oDoc.DocumentStorage.GetByName("content.xml")
                oFlux = oContent.GetInputStream()   
                ' Create XML Sax parser
                oSaxParser = createUnoService( "" )
                ' Create event manager.
                ' The names given below reflect content in accordance with
                oDocHandler = CreateUnoListener( "DocHandler_", "" )
                oSaxParser.setDocumentHandler( oDocHandler )
                ' Creation of structure in accordance with:

                oInputSource = createUnoStruct( "" )
                oInputSource.aInputStream = oFlux
                ' Tell parser to read the contents of content.xml
                ' The functions (DocHandler_) will be called in accordance with event.
                oSaxParser.parseStream( oInputSource )
                MsgBox "End of treatment." , 64, "Comment_DDE_Table()"
            End If
            MsgBox "This macro does not work for this type of document (needs to be odt Writer)" , 64, "Comment_DDE_Table()"
        End If

        MsgBox "This macro does not work for this type of document (needs to be odt Writer)" , 64, "Comment_DDE_Table()"
    End If

End Sub ' Comment_DDE_Table

' Below, the functions of listener, corresponding to events:

Sub DocHandler_startDocument()
End Sub

Sub DocHandler_endDocument()
End Sub

Sub DocHandler_startElement( cName As String, oAttributes As )

    Dim oTable As Object
    Dim oCursor As Object
    Dim oComment As Object

    Dim oDateHour As New

    ' If the element is a table, store its name
    If cName = "table:table" Then
        oTableName = oAttributes.getValueByName("table:name")
    End If   

    ' See OASIS Open Document Format for Office Applications:
    ' The DDE data connection of tables is contained in an <office:dde-source> element.
    ' The usage of this element differs between spreadsheet and text document tables. 
    ' For text document tables, the element is contained within the table's <table:table> 
    ' element directly.
    ' Hence test if the table is DDE-linked:
    If cName = "office:dde-source" Then

        ' Access the table:
        oTable = oDoc.TextTables.getByName(oTableName)

        ' Create a cursor in the first cell of the table:
        oCursor = oTable.getCellByPosition(0, 0).createTextCursor

        ' Create a comment:
        oComment = oDoc.createInstance("")

        ' Get time-stamp of right now:
        With oDateHour
            .Minutes = minute(now)
            .Hours = hour(now)
            .Day = day(now)
            .Month = month(now)
            .Year = year(now)
        End With
        ' Fill the comment with DDE source and range
        With oComment
            .Author = "Source: DDE link"
            .Content = oAttributes.getValueByName("office:dde-item") & chr(13) &_
            .DateTimeValue = oDateHour
        End With

        ' Insert the comment in the cell
    End If

End Sub ' DocHandler_startElement

Sub DocHandler_endElement( cName As String )
End Sub

Sub DocHandler_characters( cChars As String )
End Sub

Sub DocHandler_ignorableWhitespace( cWhitespace As String )
End Sub

Sub DocHandler_processingInstruction( cTarget As String, cData As String )
End Sub

Sub DocHandler_setDocumentLocator( oLocator As )
End Sub


  1. DDE: "Dynamic Data Exchange", allows you to link objects by reference to the file, without embedding them.
  2. See the standard OASIS Open Document Format for Office Applications. The use of this element differs between spreadsheet and word processing. For text documents, the element is contained directly inside the element <table:table>.
  3. See the API documentation for
  4. See the API documentation for