Ask Your Question

Macro Updating just some external links button

asked 2018-12-26 23:17:36 +0100

Albert1976 gravatar image

updated 2018-12-27 01:01:15 +0100

Ratslinger gravatar image

In calc libreoffice is possible create a button with a macro that update the external link only in specific sheet. Example: button 1 update data in sheet 1. It's possible?

I find this code but update links in all sheet.

Sub refreshAllSheetLinks()
oEnum = thisComponent.ExtrenalDocLinks.createEnumeration
while oEnum.hasMoreElements
oLink = oEnum.NextElement
End Sub

Thank you

edit retag flag offensive close merge delete


@Albert1976 Please note whenever posting on multiple forums to help eliminate duplicate efforts.

This question is also posted here.

Ratslinger gravatar imageRatslinger ( 2018-12-27 01:03:18 +0100 )edit


Where did you find that code? There is no refresh method for that - it errors. Also, Extrenal is an incorrect spelling - sb External. Further explanation of what you have and what you are attempting seems appropriate.

Ratslinger gravatar imageRatslinger ( 2018-12-27 04:00:19 +0100 )edit

ok, the code I found on an openoffice forum, I tried again and in fact it does not work, I had found another similar code, but now I can not find it anymore, but this code updated all the links of all the pages in a only once, as happens when you open the document in libreoffice and asks if you want to update the links. Now I would like to create a button that updates only the links of a single sheet. I tried to record a macro, doing edit -> link -> select links and update. But the macro does not work, it only gets to open the link window but does not update. I hope I explained myself better now.

Albert1976 gravatar imageAlbert1976 ( 2018-12-28 11:14:49 +0100 )edit


Unfortunately this does not yet explain what links you are looking to update. In Calc there are a number of different ways to link to data and each is a different process requiring different code. What is it you are linking to - cells in other Calc docs, HTML tables on web pages or what? How are your links established?

Ratslinger gravatar imageRatslinger ( 2018-12-28 18:35:39 +0100 )edit

It appears you have also posted on AOO forum and are actively getting responses there. Again, please note when posting in multiple locations so as people do not duplicate efforts. Seems you have adequate help there.

Ratslinger gravatar imageRatslinger ( 2018-12-28 19:36:39 +0100 )edit


i don't posted in AOO forum. There is a post similar of mine, but is not. I posted in stackoveflow but my post is deleted. I have some html tables imported from different URL for each sheet. I need a button that update the data of this tables . A button for each sheet.

i hope you can help with this problem.

Albert1976 gravatar imageAlbert1976 ( 2018-12-30 11:52:53 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-12-30 16:20:57 +0100

Ratslinger gravatar image

Hello @Albert1976,

First apologies for thinking that was you on AOO forum. The similarity was striking.

The code is simple but requires specific planning on your part. Start with the links themselves. The links you have should not have an automatic update set (Update every x seconds) if you are to update by a button. HTML table links are all grouped under AreaLinks and the destination sheet can be identified in DestArea.Sheet. When the appropriate link is found, the refresh() method can be applied.

Have tested this and is working using just one macro for all sheets. You will have one button for each sheet and each button will point to the same macro using the Execute action event. A key point here is naming the control. The macro will use this name to determine which links to update. In my test and in this macro the two buttons were internally named Sheet1 and Sheet2.

Here is the macro:

Option Explicit

Sub UpdateTables(oEvent)
    Dim sSheet            As String
    Dim iSheet            As Integer
    Dim iLinkCount        As Integer
    Dim x                 As Integer
    Dim oLink             As Object
 Rem Get the name of the button pushed
    sSheet = oEvent.Source.Model.Name
 Rem Set the variable to the relative sheet number depending upon the name
 Rem Sheets are numbered starting from zero
    If sSheet = "Sheet1" Then
        iSheet = 1                        Rem This is second sheet
    ElseIf sSheet = "Sheet2" Then
            iSheet = 2                    Rem This is third sheet
 Rem Obtain total number of links in the document
    iLinkCount = ThisComponent.AreaLinks.getCount()
 Rem Cycle through links looking for those on a specific sheet
    For x = iLinkCount to 1 Step -1
        oLink = ThisComponent.AreaLinks.getByIndex(x-1)
 Rem When link on specific sheet is found, refresh the link
        If oLink.DestArea.Sheet = iSheet Then
        End If
    Next x
End Sub

Of course you will need to modify the code (If statement checking for button names) and add additional buttons for the number of sheets you have in your document.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-12-26 23:17:36 +0100

Seen: 24 times

Last updated: Dec 30 '18