Update ExternalDocLinks with a macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
muhu
Posts: 6
Joined: Sun Dec 25, 2011 10:34 pm

Update ExternalDocLinks with a macro

Post by muhu »

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.
windows7 openoffice 3.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Update ExternalDocLinks with a macro

Post by Villeroy »

ThisComponent.calculateAll() [or simply Ctrl+Shift+F9]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
muhu
Posts: 6
Joined: Sun Dec 25, 2011 10:34 pm

Re: Update ExternalDocLinks with a macro

Post by muhu »

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?
windows7 openoffice 3.3
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: Update ExternalDocLinks with a macro

Post by kingfisher »

What about the setting in Tools > Options > Calc > General : Updating?
Apache OpenOffice 4.1.9 on Linux
muhu
Posts: 6
Joined: Sun Dec 25, 2011 10:34 pm

Re: Update ExternalDocLinks with a macro

Post by muhu »

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?
windows7 openoffice 3.3
muhu
Posts: 6
Joined: Sun Dec 25, 2011 10:34 pm

Re: Update ExternalDocLinks with a macro

Post by muhu »

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!
windows7 openoffice 3.3
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: Update ExternalDocLinks with a macro

Post by kingfisher »

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.
Apache OpenOffice 4.1.9 on Linux
B Marcelly
Volunteer
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: Update ExternalDocLinks with a macro

Post by B Marcelly »

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

OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
muhu
Posts: 6
Joined: Sun Dec 25, 2011 10:34 pm

Re: Update ExternalDocLinks with a macro

Post by muhu »

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?
windows7 openoffice 3.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Update ExternalDocLinks with a macro

Post by Villeroy »

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
B Marcelly
Volunteer
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: Update ExternalDocLinks with a macro

Post by B Marcelly »

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

OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
majikw
Posts: 1
Joined: Wed Feb 29, 2012 8:15 pm

Re: Update ExternalDocLinks with a macro

Post by majikw »

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.
OpenOffice 3 on Windows 8
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Update ExternalDocLinks with a macro

Post by rudolfo »

@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.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
muhu
Posts: 6
Joined: Sun Dec 25, 2011 10:34 pm

Re: Update ExternalDocLinks with a macro

Post by muhu »

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 ;-)
windows7 openoffice 3.3
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Update ExternalDocLinks with a macro

Post by Lupp »

(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.)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply