[Solved] Update external links in specific worksheet

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

[Solved] Update external links in specific worksheet

Post by Math »

greetings ,

I found a good macro from sr. "Lupp" to update links with external files, the macro updates all the external links of the active file, even with the external files closed , which, by the way, is very good for me, update with external files closed .
source: macro sub unoUpdateTableLinks

Now I need the macro to update only a specific worksheet, ie, update the external links by the Worksheet Name, need to indicate in the macro the worksheet name , then by the name of the worksheet , the macro must update the bindings only on the specific worksheet .

hugs friends .
Last edited by Math on Sun Dec 30, 2018 8:13 am, edited 5 times in total.
LibreOffice 5.4.4.2 on Windows 7
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: Update external links in specific worksheet

Post by Math »

also has a very good macro of sr. Villeroy , who can help :
sub refreshAllSheetLinks

but I need a macro to update the external links by the name of the specific worksheet .

the macro needs to know the name of the worksheet to then update only the specific worksheet .


hugs .
Last edited by Math on Mon Dec 17, 2018 2:01 pm, edited 1 time in total.
LibreOffice 5.4.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Update external links in specific worksheet

Post by Villeroy »

Who is the person who pays you for your work? I'd like to talk with that person about your lousy performance and how much that person is willing to pay for a different solution.
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
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: Update external links in specific worksheet

Post by Math »

Greetings friends ,

         the macro sub LinkASheet, makes specific spreadsheet update correctly .

         but the problem is that it puts the values on the formulas { file:///C:/ } , that is, the formulas are replaced only by the values of the external links, and then there are no more formulas .

         so I see that of model macro is not perfect because it excludes formulas from the specific spreadsheet .

quote : another reference



hugs .
Last edited by Math on Mon Dec 17, 2018 2:05 pm, edited 22 times in total.
LibreOffice 5.4.4.2 on Windows 7
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Update external links in specific worksheet

Post by mikele »

Hello math,

Sheetlinks (as well as AreaLinks, DDELinks) are something completely different then formulas like "=file://C:/...''
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Update external links in specific worksheet

Post by Lupp »

Math wrote:Another reference source to help is ... https://ask.libreoffice.org/en/question ... rough-api/
This may be a misunderstanding. I pointed you to that thread to realize what "erAck" (a senior developer of LibreOffice) tells: No way. The interface used internally by the Calc code to get access to the ExternalDocumentLinks / formula_links is not published. This means you cannot get access to it through the API.

You should really start to consider the overall design of the intended solution for whatever you are working on. If you cannot change it, you need to give up. If someone else drafted this approach you need to tell him (f/m) that there is no way.

Linking spreadsheet documents is basically a doubtable idea anyway. You should avoid it wherever possible for more than one reason. If unavoidable the design of a linked complex of spreadsheet documents needs to take in account the actual limitations from the beginning. Otherwise you are in danger to "postpone a few problematic aspects" and work out the easier parts with a lot of effort - only to finally surrender to the fact that the postponed parts are not feasible at all.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Update external links in specific worksheet

Post by Villeroy »

Database links are the only links that really work as expected -- if you expect a list. Virtually "nobody" uses spreadsheets for calculation modeling while "everybody" tries so hard to store row sets in sheets. So they end up with a lot of lists cluttered across multiple sheets (or even worse cluttered across files) and while doing so, they use links that refer to single values (DDE link), a fixed range address (AreaLink) or an entire sheet (SheetLink) with unknown dimenstions.
Database links consist of one rectangular range (row set) with one row of column labels on top.
When the size of the row set changes, the size of the database range changes accordingly. If the source is a sheet of a spreadsheet, the used range of that sheet is taken as row set which saves a lot of macro coding. If the source is another database range of a spreadsheet, the size adjusts dynamically as you insert/remove rows from the source range. If the source is a true database all these problems are absent anyway. Databases have no blank areas of unused cells.
A reference to the name of the db range always refers to the correctly sized rectangular range. =OFFSET(Import1;1;3;ROWS(Import1)-1;1) refers to the third column of Import1 without the row of column labels.
DB ranges can be forced to separate linked data from formatting.
DB ranges can be forced to insert new rows when expanding. This prevents that they flush over existing sheet data. Likewise, they delete rows when shrinking.
DB ranges can even adjust adjacent formulas cells (calculated fields) to the actual size of the imported range.
You can update them independently from other db ranges in the same document or even on the same sheet. For the macro artists: ThisComponent.DatabaseRanges.getByName("Import1").refresh() does everything you want fully automatically. Without any macro, you can bind the refresh command to toolbars, menu items and shortcuts.
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
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: Update external links in specific worksheet

Post by Math »

in another forum has the macro sub LinkASheet

this macro deletes the formulas of links and puts the values in place of the formulas, thus, there are no more formulas for links, so the macro is not perfect .
LibreOffice 5.4.4.2 on Windows 7
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Update external links in specific worksheet

Post by Lupp »

Math wrote:in another forum has the macro sub LinkASheet {was linked}.
This macro deletes the formulas of links and puts the values in place of the formulas, thus, there are no more formulas for links, so the macro is not perfect .
The "macro" neither updates FormulaLinks which are formulae containing direct references to external data, nor does it replace formulae with values.
It is taken without the due indication of source from the famous book "OpenOffice Macros Explained" by Andrew Pitonyak. It even contains the copied page number 479. In the third edition the example is given in Lising 425 (p 482) and is introduced with the following words:
Andrew Pitonyak wrote:The macro in Listing 425 creates a sheet named “LinkIt” and then links to a sheet in a specified external
document. If the “LinkIt” sheet already exists, the link is obtained from the spreadsheet document and the
link is refreshed. Refreshing a link causes the data linked into the current document to be updated.
This is exactly what the Sub does.
SheetLinked sheets never contain external references, but only the data. Their being linked is managed on the docment level, not by formulae.

Make sure to understand that a SheetLink is fundamentally different from what you asked for. In addition you got answers already concerning the question how to refresh a SheetLink.

Regarding references to external data the statement by Mike Kaganski Eike Rathke I pointed you to is terminatory. He knows what he's talking of. Continuing on this trail without absolutely profound knowledge (superior to the knowledge of a senior developer) is ...
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Update external links in specific worksheet

Post by mikele »

Hi,
a simple workaround could (maybe?) be the following:

Code: Select all

Sub Snippet
	Dim oSheets As Variant
	Dim oObj1 As Variant
	Dim oObj2 As Variant
	Dim sAbsoluteName As String
	Dim oObj3 As Variant
	Dim sFormula As String
	
	oSheets = ThisComponent.getSheets()
	oObj1 = oSheets.getByIndex(0)
	oObj2 = oObj1.queryFormulaCells(7)
	for i=0 to oObj2.count-1
		oObj3 = oObj2.getByIndex(i)
		sFormula = oObj3.getFormula()
		if instr(sFormula,"'file:///") then
			oObj3.setformula(sFormula)
		end if
	next	  
End Sub
All formulas in 1st sheet (with "'file:///") will be rewritten and recalculated. Does it make sense - ??? (I'm not sure ...)
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: Update external links in specific worksheet

Post by Math »

hi mikele ,

Error occurred : Property or method not found: getFormula

Code: Select all

sFormula = oObj3.getFormula()
hugs.
LibreOffice 5.4.4.2 on Windows 7
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Update external links in specific worksheet

Post by mikele »

Hi,
I can't imagine why ...
Please upload a sample file.
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Update external links in specific worksheet

Post by Villeroy »

queryFormulaCells returns a collection of ranges.

http://www.openoffice.org/api/docs/comm ... anges.html

If the ranges happen to be single cells, they have a value, a string and a formula.
A range is a rectangle of many cells. A range has no value, string nor formula.
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
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Update external links in specific worksheet

Post by mikele »

Hello Villeroy,
thanks. Then it's obvious to iterate through the ranges.

Code: Select all

Sub Snippet
	Dim oSheets As Variant
	Dim oObj1 As Variant
	Dim oObj2 As Variant
	Dim oObj3 As Variant
	Dim sFormula As String
	oSheets = ThisComponent.getSheets()
	oObj1 = oSheets.getByIndex(0)
	oObj2 = oObj1.queryFormulaCells(7).getCells.createEnumeration
	do while oObj2.hasMoreElements
		oObj3 = oObj2.nextElement
		sFormula = oObj3.getFormula()
		if instr(sFormula,"'file:///") then
			oObj3.setformula(sFormula)
		end if
	loop
End Sub
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Update external links in specific worksheet

Post by Villeroy »

This is inefficient. Try with many thousands of cells.
And then try this:
menu:Edit>Find&Replace
[More Optoins]
[X] Regular expressions
[X] Search in formulas
Search: .+
Replace: &
[Replace All]
This replaces any cell contents on the whole sheet with itself and it is much faster than your macro. Side effect: it may convert numeric text to numbers.
The following replaces only formulas starting with = and having file:/// in it:
Search: ^=(.*)(file\:///.*)
Replace: =$1$2

If this works for you, you don't really need a macro anymore or write a macro that performs this replacement.
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
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: Update external links in specific worksheet

Post by Math »

greetings friends ,

follows attachment files for Test .

the main file is called "Vínculos" .

update only worksheet 3 of the main file .


hugs .
Attachments
Teste.rar
(53.44 KiB) Downloaded 230 times
LibreOffice 5.4.4.2 on Windows 7
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Update external links in specific worksheet

Post by mikele »

Hello,
@Villeroy: you're right. I took it as workaround and just to find out if it works.
A little bit better performance(?) than my last proposal:

Code: Select all

Sub Snippet
   Dim oSheets As Variant
   Dim oObj1 As Variant
   Dim oObj2 As Variant
   Dim sAbsoluteName As String
   Dim oObj3 As Variant
   Dim sFormula As String
   
   oSheets = ThisComponent.getSheets()
   oObj1 = oSheets.getByIndex(0)
   oObj2 = oObj1.queryFormulaCells(7)
   for i=0 to oObj2.count-1
      oObj3 = oObj2.getByIndex(i)
      aFormula = oObj3.getFormulaArray
      oObj3.setFormulaArray(aFormula)
   next     
End Sub
But the best will be: search & replace (even via macro).
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Update external links in specific worksheet

Post by Villeroy »

Yes, setting the FormulaArray is very efficient unless the formulas are cluttered across thousands of ranges which is very unlikely. Every single API access takes a lot of CPU time. For efficiency, you should call methods that get/set a lot of data at once.

Code: Select all

    oDesc = rg.createReplaceDescriptor()
    oDesc.setSearchString("^=(.*)(file\:///.*)")
    oDesc.setReplaceString("=$1$2")
    oDesc.SearchRegularExpression = True
    rg.replaceAll(oDesc)
The rg variable could be a range or sheet. A sheet object is a range with some extra properties and methods. Everything you can do with a range is possible with a sheet as well.
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
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Update external links in specific worksheet

Post by Lupp »

@mikele, @Villeroy:

(I'm not talking of sheet-links or area-links... They are also internalised but behave different and can be refreshed by API methods.)

As you surely know values needed for references into other documents (ExternalDocumentLinks) are always internalised in a special kind of hidden tables (sheets) that get saved with the document. The values are refreshed if permitted "onReload" or when the UI is used to do so explicitly ('Edit...'). As opposed to the ways AreaLinks and Sheetlinks are treated, the respective methods for formula links are not 'Published' to the API and therefore not callable by user code.

As long as the document stays open the internalised values are kept and referenced in place of the external references. A newly created external reference to a cell will not cause the external document to be opened if an internalised copy is available. This holds if the formulae containing the references are edited or deleted in-between.

My experiences are mainly with LibO. Nevertheless I feel sure that also in AOO Calc the workaround diskussed above can not do as expected.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Update external links in specific worksheet

Post by Villeroy »

You are right. It can not work this way. It becomes obvious when I test with a NOW() formula. Linked spreadsheets is a no-go.
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
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Update ex (ternal links in specific worksheet

Post by mikele »

Hello,
I'm not sure if we discuss the same.
If I have a document A with a formula

Code: Select all

='file:///path_to_File1.ods'#$Sheet1.B2
. This link (resp. its value) is refreshed (permitted by user) during opening document A. When I now open File1.ods and change the value of Sheet1.B2 the link in document A isn't refreshed (that's normal - I know). But when I run one of the discussed macros (which rewite the formula), the value is refreshed immedately (tested under LO5.4).
No idea if this is usefull. Pressing F9 is simplier but recalculates all sheets ...
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: Update external links in specific worksheet

Post by Math »

[SOLVED]

              I found a solution to update the specific spreadsheet . :D

              first open the "File1" with the macro To open a file

             then call the macro Sub Snippet

I thank all the friends for their collaboration and help . :bravo: :super:
LibreOffice 5.4.4.2 on Windows 7
Math
Posts: 89
Joined: Mon Oct 29, 2018 6:32 pm

Re: [Solved] Update external links in specific worksheet

Post by Math »

> the Macro below will perform the following operation :
- Open the external file with the information to update the worksheet .
- Updates the Specific Worksheet .
- Close the external file that was opened .

Code: Select all

Sub Update_Specific_Worksheet
Dim oSource As Object, oCible As Object
Dim oProps() As New  com.sun.star.beans.PropertyValue
Dim URLSource As String
Dim document As Object, dispatcher as Object
URLSource = ConvertToURL("C:/Users/Documents/Teste/File.ods")
oSource = StarDesktop.loadComponentFromURL(URLSource, "_blank", 0, Array())
FileURL = convertToURL(URLSource)
Call Snippet   'macro Call to update specific worksheet
document   = oSource.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:CloseDoc", "", 0, array())
End Sub
Attention :
  - you need to keep the files involved in your Home Directories .
  - if you change directory to any involved file, then in the first update, manually make the new link links between the main file and the files that have changed location .
LibreOffice 5.4.4.2 on Windows 7
Post Reply