Editing multiple hyperlinks

English
,
#1

I am running LibreOffice Calc Version: 6.4.7.2 on Linux Mint 20.2 Cinnamon. I have a spreadsheet with hundreds of cells containing hyperlinks to various documents on my hard drive. I would like to rename the parent folder containing most of these documents to something more meaningful to me. Unfortunately, making this folder name change renders all the hyperlinks on my Calc sheet inoperable. When I edit any of the hyperlinks (which I created using Insert > Hyperlink), the Document Path: still shows the previous (now changed) folder name.

If I want to change this folder name and still have my hyperlinks work, do I have to edit each hyperlink path one by one, or is there an easier way?

#3

By its structure, the .ods file is an archive (if you add the .zip extension, you will be able to use any archiver program). Look inside the archive for the content.xml file. Perhaps it will be possible to make text replacements in this file.

#4

Maybe you can use the Find and Replace… function.
Can you edit your question and share a sample of the actual hyperlinks and how they must be with the new folder name? Thanks.

#7

I am sort of a newbie when it comes to getting under the hood of Linux and LibreOffice. I was able to extract the content.xml file but couldn’t view it with an ordinary text editor such as Notepad. I was able to view it with LibreOffice Writer and BaseX. The information is there all right, and I can edit the text, but I am clueless as to saving it as a valid .xml file or zipping the whole thing back up into an .ods file.

#9

Hallo
Install APSO from APSO - Alternative Script Organizer for Python » Extensions.

Copy & Paste the Code provided in my Answer into new module, edit the 2 obvious lines and run it from your Calc-document in Question.

#17

Sokol92 had the solution that ultimately worked for me. At first I got frustrated trying to edit the content.xml file using Notepad, Text Editor, and Vim. Emacs kept freezing up on me. I guess I’m a GUI type of guy, a user rather than a tinkerer. Finally tried using sed on good old reliable Linux Terminal. Worked like a charm. Zipped up the files again using Compress in the Nemo context menu, and now all my hyperlinks work like I want in my Calc spreadsheet.
Thanks very much to all who lent me their help on this question!

1 Like
#2

Fur the future working:

Create the hĂ­perlins by the Cell function HYPERLINK(). Then you will able to combine the parts of the strings based on a reference to an another cell, what contains the actual Path stting.

#5

[Calc, Basic] Introspective cell functions (View topic) • Apache OpenOffice Community Forum is a collection of user-defined Basic functions to fix broken spreadsheets.
One of the functions is CELL_URL which extracts an URL from a cell. All these functions use a special addressing.
=CELL_URL(1;1;1;2) extracts the second URL from 1st sheet, 1st row, 1st column. The 4th argument is optional, default 1.
Relative addressing this sheet, this row, previous column:
=CELL_URL(SHEET();ROW();COLUMN()-1)

You can convert the extracted URL to constant text via copy/paste-special and then use the HYPERLINK function to generate valid hyperlinks.

#6

I did some test with this:

from pathlib import Path

old = "ugly_folder_name"
new = "good_folder_name"


def update_links(*_):
    doc = XSCRIPTCONTEXT.getDocument( )
    for sheet in doc.Sheets:
        specials = sheet.queryContentCells( 512 )
        for cell in specials.Cells:
            for field in cell.TextFields:
                try:
                    p = Path(field.URL) 
                    #maybe no Attribute: 'URL' -> except AttributeError
                    parts = list(p.parts)
                    parts[ parts.index(old) ] = new 
                    #maybe no 'old' in parts  -> except ValueError
                    p = Path( *parts )
                    field.URL = str( p )
                except ( AttributeError, ValueError ):
                    pass

Seems it works

#8

I assume you’re talking about running this as a macro within my spreadsheet. I’ve only done some basic fiddling around with macros, but I copied and pasted this code into a module, inserting my own folder names of course. When I tried to run it, though, I immediately got a syntax error at “pathlib”. The error message said
BASIC syntax error. Expected: ,.

I am not familiar enough with LO Basic syntax to know what the problem is. You saw the versions of Calc and Linux Mint I’m running. Don’t know if this would make any difference.

#10

See my Comment above…

#11

Got the following message when I tried to install APSO v1.2.8 (the latest) to LO Calc. Screenshot from 2021-08-29 17-13-32
It seems like it might have tried to install nevertheless, but it shows up in Extension Manager grayed out. When I click on Enable the same message pops up again. Anyway, running the macro again still results in the same syntax error. Is there some option not set properly in the macro editor or some extra code that I need?

#12

Uhh, I had similar Error-Message on trying update APSO to the latest 1.3.1 from

If nothing else works… close Libreoffice and delete
/home/ you /.config/libreoffice/4/user/uno_packages/cache
and reinstall your private Extensions.

#13

Well, sorry, tried that and got practically the same result. Trying to install the APSO package from your latest link as well as the previous link still resulted in an error message, with only some characters after uno_packages/ being different in the message.
Screenshot from 2021-08-29 20-35-04
And I did delete the cache folder like you said. Also tried to run the macro again with the same negative result.

#14

Sorry, may be you need first install libreoffice-script-provider-python from your Distro:

sudo apt-get install libreoffice-script-provider-python

after that try to c&p the provided Code into path:
/home/ you /.config/libreoffice/4/user/Scripts/python/fixlinks.py
and run it via:
→Tools→Macros→Makros execute…→→MyMacros→fixlinks→update_links

#15

I installed libreoffice-script-provider-python with no problem. However, within the folder /user there is no folder /Scripts on my computer. Do I have to create the folders /Scripts/python and the file fixlinks.py? Also, the menu items in my version of Calc aren’t →Tools→Macros→Makros execute, etc… My version of Calc is
Screenshot from 2021-08-29 22-18-31

#16

yes of course!

my LO (with GUI switched to en-US:
Version: 7.0.4.2
Build ID: 00(Build:2)
CPU threads: 4; OS: Linux 5.10; UI render: default; VCL: gtk3
Locale: de-DE (de_DE.UTF-8); UI: en-US
Raspbian package version: 1:7.0.4-4+rpi1
Calc: threaded

and the exact menu-entries:
→Tools→Macros→run Makros→→My Macros→fixlinks→→update_links

#18

Karolus, thanks for all your help even though I ultimately went with sokol92’s solution (see above). I would like to learn how to write macro’s in LibreOffice Basic. Even though I don’t like Windows OS, I must admit that writing macros with VBA in Excel was more user friendly. At least there were more books and help files on it.

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