How to remove external links with BASIC macro in calc spreadsheet?

English
,
#1

I need to export calc documents where I don’t want to keep the external links so I’m looking for the Basic macro code to remove the links (after updating it).

For updating external links in calc I found the following code

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

Thanks a lot, Johann
here: How do I create a macro that will update external links? - #2 by anon73440385

What is the corresponding code for deleting the links?

#2

I think you may need to use fields and properties.

In the Python Intercept Context Menu example. The has_url method hints that the url can be gotten from the field.


Once the url is gotten the cell value should be able to be directly set as a string. Maybe this will get you going in the correct direction.

#3

How have these links been created? There are

  1. Formulas with references like ='file:///path/doc.ods'#Sheet.A1:X99
  2. Formulas with references like =DDE("soffice";"/path/doc.ods";Sheet.A1:X99)
  3. Sheet>Link to external data
  4. Sheet>Insert sheet from file…
  5. Linked database ranges dragged from the data source window.
".uno:UpdateTableLinks"

updates all of the above except #5.

#1 and #2 can’t be broken until the corresponding formulas have been removed.

All these links serve different purposes with different pros and cons, and you may not want to remove all of them.

#4

I think the topic is about links of this type.
The Break Link button from the Menu / Edit / Links To External Files dialog replaces formulas with values ​​in cells containing the specified links. I don’t know of a direct analogue to perform the specified action using a macro.


If you use a macro to find and replace all formulas with links of type 1. with values, then after saving and reopening the document will not contain external links of the specified type.

#5

Remove all area links (menu:Sheet>Link to external data…)

oLinks = ThisComponent.AreaLinks
x = oLinks.getCount()
do while x > 0
  oLinks.removeByIndex(0)
  x = x -1
loop

Remove all sheet links (menu:Sheet>Insert sheet from file…)

for each sh in ThisComponent.Sheets
  sh.setLinkMode(com.sun.star.sheet.SheetLinkMode.NORMAL)
next
#6

For ThisComponent.ExternalDocLinks I do not know a method to break the element. :slight_smile:

#7

What’s that? If it is point 1 or 2 of my above list, you’ve got to replace the corresponding formula tokens, otherwise the links reappear with every recalculation.

For completeness, linked database ranges (point 5) can be unlinked without removing the entry in menu:Data>Define…

for each dbr in thiscomponent.databaseranges()
	vIM = com.sun.star.sheet.DataImportMode.NONE
	vST = com.sun.star.sheet.DataImportMode.NONE
	for each p in dbr.ImportDescriptor
		if p.Name = "ImportMode" then 
			p.Value = vIM
		elseif p.Name = "SourceType" then
			p.Value = vST
		endif
	next
next

The code to unlink a linked pivot table might be similar.

#8

ExternalDocLinks. The getElementNames method returns a list of links to external documents.

#9

I see. ExternalDocLinks refer to point 1. I get one when I paste-special a cell range into another document with option “Link”.
If you use something like
=LOOKUP(A1 ; 'file:///path/doc.ods'#$Sheet1.A1:A99;'file:///path/doc.ods'#$Sheet1.X1:X99; then you can not remove that link without resolving the two dependencies in this formula. Documents with ExternalDocLinks can be sent to other users because the document contains data copies of the referenced cell ranges. The recipient of such document must not update this link unless he wants to update the link his own valid source document.

#10

The Break Link button (as I wrote above) changes all formulas containing links to the element selected in the Edit Links dialog to values ​​(in Excel, BreakLink performs the same function). After that, we can send the document to another user (with values ​​instead of formulas).

#11

which results in
broken_link.ods (22.5 KB)

#12

How did you get such a monster?

1 Like
#13

Copy some list.
Get new document.
Paste-special “As Link”
The result is an array formula {='file:///path/other.ods'#Sheet1A1:G1002}
menuEdit>Links… [Break Link]

Another sample with a lookup formula. The left part is what’s left of the broken link. I recreated that part on the right side.
broken_link2.ods (39.2 KB)

#14

Thanks for the example!
I think this bug should be reported.
In Excel, when performing such an experiment, a message is displayed that the formulas will be replaced with values. If the user answers positively, the replacement is performed correctly.

#15

The whole feature is a bug in itself. It should never be used.

#16

When preparing files for transfer to other people, we always change formulas with links to external files to values ​​(Excel, LibreOffice).

#17

Well, but what if the references are formula tokens or arrays? And how do you handle expanding source ranges? Any external reference to 'file:///path/doc.ods'#Sheet1.A1:X99 does not expand when you insert new rows into the referenced source range.
The only link which is able to deal with all this is the linked database range. You don’t even need to insert any rows into the source range. If the source table is a sheet, Base reads the used area of that sheet. As a matter of course, people hate database documents, database ranges, database queries and anything that starts with “database” while trying to make a database out of their spreadsheets.

#18

The algorithm is simple: any formula containing a link to an external file is changed to a value.
We send ready-made reports that our correspondent is not going to recalculate (and will not be able to, if we have replaced the formulas with values).
You and I sometimes have different contexts in mind. :slight_smile:

#19

Which is how it should be. The undocumented problem remains that these references do not expand. Inserting a row in the source range does not expand 'file:///path/doc.ods'#$Sheet1.A1:A99 to 'file:///path/doc.ods'#$Sheet1.A1:A100

#20

Thank you, I’ll try to watch it tomorrow.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.