Macro Updating just some external links button

[Albert1976]

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
oLink.refresh
wend
End Sub

Thank you

[Ratslinger]

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

This question is also posted here.

[Ratslinger]

@Albert1976,

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.

[Albert1976]

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.

[Ratslinger]

@Albert1976

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]

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.

[Albert1976]

Hi,

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.

[Ratslinger]

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
	EndIF
 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
	        oLink.refresh()
	    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.

[negos]

Thank you!