How do I create a macro that will update external links?

scooterbird · June 16, 2020, 5:04pm

Hello, all. I have a Calc document which displays the time [NOW(); formatted as HH:MM:SS] and the weather [external link to forecast.weather.gov]. I am hitting F9 to recalculate for the current time and going to Edit > Links to External Files… > Update in order to update the temperature. I would like to create a macro to do both tasks and link it to a button which I will create using Form Controls.

I enabled Record Macro to create the macro and got this:

sub update
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:Calculate", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:EditLinks", "", 0, Array())


end sub

This resulted in the time updating, the requester for Edit Links appearing on the screen, and nothing else.

I have upgraded to 6.3.6.2 based on other question resolutions I have seen here; now the macro does nothing at all.

Macro Security has been temporarily downgraded to Low, and I have designated the containing folder of the Calc document (which is my Windows 10 Desktop) as a Trusted Source under Macro Security.

Any assistance would be appreciated. Thanks.

anon73440385 · June 16, 2020, 5:38pm

Hello,

may be

Sub UpdateExternalLinks()

  dim document   as object
  dim dispatcher as object

  document   = ThisComponent.CurrentController.Frame
  dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

  dispatcher.executeDispatch(document, ".uno:UpdateTableLinks", "", 0, Array())

End Sub

is what you are looking for.

Updating NOW() is not required, since it is a volatile function, which recalculates on any change of the sheet. Therefor e updating external links will also update NOW().

In additition for that to work you need to:

Tools -> Options... -> LibreOffice Calc -> General -> Section: Update Links when opening - Option: [x] Always (from trusted locations)
Tools -> Options... -> LibreOffice -> Security -> Button: Macro Security -> Tab: Trusted Sources -> Pane: Trusted File Locations -> Button: Add and select the directory where your file is stored.

Tested using LibreOffice:

Version: 6.4.4.2, Build ID: 3d775be2011f3886db32dfd395a6a6d1ca2630ff
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kf5; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US, Calc: threaded

Hope that helps.

ricardohda · July 14, 2024, 11:48am

It worked!! I was looking for a solution to this for a long time. I even tried AI. Can you tell me where it is possible to study, in depth, this programming language used in Calc? Thank you very much.

Ash733 · July 14, 2024, 12:28pm

https://wiki.documentfoundation.org/Documentation/BASIC_Guide#LibreOffice_BASIC_Programming_Guide

You can also use javascript, python, etc

ricardohda · July 14, 2024, 12:44pm

I really appreciate it.

scooterbird · June 17, 2020, 3:57pm

I’ve reached a solution that is somewhat different from the one proposed.

First, one of the major problems was that LO was not recognizing the installation of JRE under Tools > Options > LibreOffice > Advanced; it was apparently installed under a different user profile on this system. Corrected that with an install of JRE 9.0.1, the installer of which I still had on disk. Second, I did have to change the updating under Tools > Options > LibreOffice Calc > General, which was on “On Request”. (The option for Trusted Source was already enabled, as I described in my question.)

Finally, UpdateTableLinks (which apparently was SheetLinks in what was presumably an earlier version of Basic) does not recalculate volatile functions, so it was necessary to reinstall the line

dispatcher.executeDispatch(document, ".uno:Calculate", "", 0, Array())

back into the proposed script. Once this was done, it ran correctly. I was then able to bind it to the mouse click on a form push button.

Thanks for your assistance.

anon73440385 · June 17, 2020, 4:11pm

Finally, UpdateTableLinks (which apparently was SheetLinks in what was presumably an earlier version of Basic) does not recalculate volatile function

It does on my version, otherwise I wouldn’t have posted the version I’ve. I just assume that the update did not work and hence the volatile function doesn’t update - it isnt the UpdateTableLinks which updates the function, the function updates because there are changes in the sheet. Hence this means: If there are no updates (regardless of their origin) to the sheet, then NOW() doesn’t update.

> Second, I did have to change the updating under Tools > Options > LibreOffice Calc > General, which was on "On Request".

What do you think that

Tools -> Options... -> LibreOffice Calc -> General -> Section: Update Links when opening - Option: [x] Always (from trusted locations)

does mean in my answer? Hence I can’t see were exactly your solution is somewhat different.