Apache OpenOffice Community Forum

Posted: **Sat Dec 31, 2011 8:21 pm**

Hi all,

I have found Villeroy's toturial about ExternalLinks in Calc here: http://user.services.openoffice.org/en/ ... =75&t=3294
and his macro to update Links here: http://user.services.openoffice.org/en/ ... =20&t=6004

I have a Spreadsheet with many Links to other spreadsheets and I want to write a macro that allows the user to update all the links with the help of a button without going to "Edit>Link...>", marking all links and then clicking "update".

I tried Villeroy's macro:

Code: Select all

Sub refreshAllSheetLinks()
oEnum = thisComponent.AreaLinks.createEnumeration
while oEnum.hasMoreElements
oLink = oEnum.NextElement
oLink.refresh
wend
oEnum = thisComponent.SheetLinks.createEnumeration
while oEnum.hasMoreElements
oLink = oEnum.NextElement
oLink.refresh
wend
oEnum = thisComponent.DDELinks.createEnumeration
while oEnum.hasMoreElements
oLink = oEnum.NextElement
oLink.refresh
wend
End Sub

But this didn't work for me. With the help of the debugger I found out that the While-loops aren't entered, so I thought that the links I use are stored as another Object-type. I looked in google and found that the Sheet has the property "ExternalDocLinks".
Then I changed the macro to:

Code: Select all

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

But I get an error message that there is no mehtod "refresh". I looked in the api and only found that http://api.openoffice.org/docs/common/r ... Links.html
Because I didn't find any refresh/update method I tried to look in the dbg_methods property, but there I didn't find an update/refresh method.

Could someone pls tell which is the correct method to update the externaldoclinks - I searched the internet for hours but only found Villeroy's macro in several posts.
Thanks very much for your help.

Posted: **Sun Jan 01, 2012 12:23 am**

ThisComponent.calculateAll() [or simply Ctrl+Shift+F9]

Posted: **Sun Jan 01, 2012 11:13 am**

Villeroy, thanks for the reply. I tired your sugestion but it didn't update my links.

I create the links with the help of a macro:

Code: Select all

 dataPath = "='file:///" & path & "/urlaubsscheck " & ydate & " " & uList.getCellByPosition(1,ncounter).String & " " & uList.getCellByPosition(2,ncounter).String &".ods"& "'#$Lernerkalender." & cPos
      cCell =cList.getCellbyPosition(hcounter,vcounter)
      cCell.Formula = dataPath

I thought I could update these links by seting/creating them again with the help of the macro - but this doesn' t happen.
The links are only updated when you open the file again and select "yes" when you're asked whether the links should be updated or not [or you go to "Edit>Links...>Update"]
What happens when I click "yes", when I am asked whether to update the links alternatively when I go to "Edit>Links...>Update" (sure the same) - how can I do the same with the help of a macro?

Posted: **Sun Jan 01, 2012 12:37 pm**

What about the setting in Tools > Options > Calc > General : Updating?

Posted: **Sun Jan 01, 2012 2:52 pm**

Thanks for the hint kingfischer, I just have tried it. I set the update option and then opened both sheets, the mastersheet that contains the links and the datasheet.
When I changed the data in the datasheet the links weren't updated immediatly/automaticaly, only after I went to "Edit>Links...>Update".

But actually I want to use a macro because right after the update of the links a macro should start, that changes the cellbackground according to the conent of the cells.
Of course I could tell the user in a kind of an instruction to first update the links via "Edit>Links...>Update" and than run the cellbackground macro by clicking on a seperate button, but I really would prefer to do it all with the help of a macro.
Another aspect is that some of the target user of my sheet use Lotus Symphony and there I couldn't find any setting for automatically updating links.

I thought the update of ExternalDocLinks would work analogously to the update of the other link types in Villeroy's macro. So I changed the code like this:
Sub refreshAllSheetLinks()
oEnum = thisComponent.ExtrenalDocLinks.createEnumeration
while oEnum.hasMoreElements
oLink = oEnum.NextElement
oLink.refresh
wend
End Sub

But as I wrote in my first post this didn't work.

I wondered how I could find out that there is a method called "refresh" and for which Linktypes it works. I looked in the IDL Reference, and saw that e.g. "Sheetlink" epxports the interface "XRefreshable" and there the method "refresh" is implemented. But ExternalDocLink doesn't have the interface "XRefreshable".
So what happens when I go to "Edit>Links...>Update" and how can I do this in a macro?

Posted: **Fri Jan 06, 2012 10:31 pm**

Can anyone help me pls?
Or do any one know an advanced developer who could help me, and can send me his email in forum message?
I really appreciate any help!

Posted: **Sat Jan 07, 2012 3:02 am**

I drew a blank. I looked at several api pages such as XExternalDocLink and XExternalDocLinks but was unable to find any method to update (a) link/s.

Andrew Pitonyak or someone like him may be able to help for a fee.

Posted: **Thu Jan 12, 2012 10:00 am**

Hi,
As far as I have seen, there is no way to refresh cell links (those in ExternalDocLinks) with the API. No way either with the dispatcher. You may issue an improvement request at Bugzilla.

muhu wrote:I have a Spreadsheet with many Links to other spreadsheets and I want to write a macro that allows the user to update all the links with the help of a button without going to "Edit>Link...>", marking all links and then clicking "update".

That is easy, without macro : menu File > Reload. The user will be asked if he/she wants to update links.
From a macro, use the dispatcher:

Code: Select all

dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:Reload", "", 0, Array())

Of course save your changes before reload.

Posted: **Fri Jan 13, 2012 1:06 am**

Thanks very much for your help!

If I understand all this correctly, the refresh of ExternalDocLinks is not possible via a macro because this hasn't been implemented in the api?
So is there another possibility to update the ExternalDocLinks automatically e.g. by writting a plug-in in java? Because it is possible in ooo - I can click "Edit>Links...>Update" and then the links are UPDATED! I thought I just have to do what happens when I click on "Update" in the "Links-Dialog"...

Further I am wondering:
I want to write this macro, because I want to run another macro rigth after the Update-process, is there possibility to track if the user has updated the lins (clicked on "Update")?

Another alternative solution could also be: Can I use another link type that can be updated by a macro? I will go thorugh Villeroy's tutorial again and try some other link types. Here are some thoughts and questions I have spontanously:
sheetlinks (2. Linked Sheet From File): is only possible if I copy the whole sheet of the source file - I don't want this because then my file would get to big. However how can I create this type via a macro?
arealinks (3. Import External Data By Name): That would work, because it can be updated by Villeroy's macro. But HOW can I create the link via a macro?

Posted: **Fri Jan 13, 2012 2:35 am**

I stopped using all this bullshit. Since I can work with database engines I will never again use growing data collections in linked spreadsheets. This can not work reliably therefore it is not worth all the macro coding.

Posted: **Fri Jan 13, 2012 9:43 am**

muhu wrote:If I understand all this correctly, the refresh of ExternalDocLinks is not possible via a macro because this hasn't been implemented in the api?

Exactly.

muhu wrote:So is there another possibility to update the ExternalDocLinks automatically e.g. by writting a plug-in in java? Because it is possible in ooo - I can click "Edit>Links...>Update" and then the links are UPDATED! I thought I just have to do what happens when I click on "Update" in the "Links-Dialog"...

No, because a plug-in (in java or any other language) would use the API. Edit>Links...>Update is internal stuff, not accessible externally.

Posted: **Wed Feb 29, 2012 8:20 pm**

Why not just use Macro Recorder as part of the developers pack? I have 3 external links, I started the recorder, clicked on the "Data - Edit Links" and refreshed my external links, and then stopped the recording... here is the code:

Sub UpdateLinks()
'
' UpdateLinks Macro
'

'
ActiveWorkbook.UpdateLink Name:= _
"\\URL-HERE\NYR-Log.xlsm", Type:=xlExcelLinks
ActiveWorkbook.UpdateLink Name:= _
"\\URL-HERE\INBOUND Dock Yard Status.xlsm", Type:= _
xlExcelLinks
ActiveWorkbook.UpdateLink Name:= _
"\\URL-HERE\Get_Dockmaster_Data.xlsm", Type:= _
xlExcelLinks
End Sub

works like a champ for me.... good luck with your project.

Posted: **Wed Feb 29, 2012 10:00 pm**

@majikw, even if it works for you, it will be pretty much useless for the original poster, because it is a VBA Excel Macro. And the OP looks for a solution in OpenOffice Calc. And be assured that I would have been the first one to ask him, what he has been smoking if he'd asked for an Excel solution in an OpenOffice forum.

Posted: **Sat Mar 03, 2012 8:22 pm**

The macro recorder was the first thing I tried after I wasn't able to update the links with a self written macro.
This didn't work for me, because the recorded macro only opened the window to update the links, but didn't update them.
Anyway thanks for the suggestion.

At the moment I don't have time to work on this issue, but if I find out a workaround I will post it here.
But please don't stop posting, I will keep following your posts

Posted: **Mon Jul 03, 2017 7:14 pm**

(I came about this old thread by accident.)
There is an uno command ".uno:UpdateTableLinks" for the purpose. It is executable in the way shown below.
The code was derived from a simple recorded macro by replacing the command.

Code: Select all

sub unoUpdateTableLinks()
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:UpdateTableLinks", "", 0, Array())
end sub

".uno:UpdateTableLinks" does not accept any arguments. As far as I can see this results in making it impossible to finish the update without answering a prompt. In addition the changed source file must be saved. (It may be open, however.)

Apache OpenOffice Community Forum

Update ExternalDocLinks with a macro

Update ExternalDocLinks with a macro

Re: Update ExternalDocLinks with a macro

Re: Update ExternalDocLinks with a macro

Re: Update ExternalDocLinks with a macro

Re: Update ExternalDocLinks with a macro

Re: Update ExternalDocLinks with a macro

Re: Update ExternalDocLinks with a macro

Re: Update ExternalDocLinks with a macro

Re: Update ExternalDocLinks with a macro

Re: Update ExternalDocLinks with a macro

Re: Update ExternalDocLinks with a macro

Re: Update ExternalDocLinks with a macro

Re: Update ExternalDocLinks with a macro

Re: Update ExternalDocLinks with a macro

Re: Update ExternalDocLinks with a macro