I got a file (Excel .xlsx) with instructions in a cell like this .:
_xlfn.image(“https://…”)
When I want to see the image in LibreOffice, an error message appears (slightly different depending on the LO version) “#NAME?”.
When I look at “Edit / Link to external files”. Is that text grayed out and not selectable.
How can an image display in Calc from an external link?
Is it possible to convert this instruction from MS-excel to LO-calc
“Base” is the database component of LibreOffice. Calc is a spreadsheet, and it is not Excel, which tries to be a database.
For a demo, download https://www.mediafire.com/file/b4p5155c1y8bhoq/Headshots.zip/file
Extract the zip file, open the database document and the form therein.
Edit a Hyperlink:
You can define a hyperlink from an image to another location in your spreadsheet, another document, or a web page.
Select (Insert or) Edit > Hyperlink in the context menu to open the Hyperlink dialog. For more informations:
https://books.libreoffice.org/en/CG24/CG2406-UsingGraphics.html
https://books.libreoffice.org/en/CG24/CG2411-LinkingCalcData.html
Maybe I’m bad to explain my wish?
I try again.
I have received a spreadsheet (???.xlsx)
This spreadsheet contains information, some formulas, and cells with the following instruction .: _xlfn.image(“https://…”).
When the https://… address is copied into a browser, the image is displayed. (but in Calc there are “errors” in the cells with this instruction from Excel)
Is that instruction from Excel possible to convert to Calc? (or should I handle xlsx files with Base?)
Thank you!
(I tried opening .xlsx with Base, but calc still opens)
Maybe a hyperlink is a way to solve this.
But…
I don’t want to configure about 400 links manually.
With hyperlink, image is not displayed in the cell, (you need to press ctrl+click to see the image)
Is there an alternative to this above in LO?
Can Open Office help me? or is Excel the only solution to solve this?
Wondering if it’s possible to write a formula that creates a clickable link (if possible) - but I’m stuck.
Assume the following formula is in cell A11
=_xlfn.image("https://7046381.app.netsuite.com/core/media/media.nl?id=1247097&c=7046381&h=nlNlnwxmusc1ujAAB9hy3-KdOGd2TpIzAc8Ev2Ww_2vNaQx6")
All attempts to read/manage this information have failed.
=LEN(A11) give the message “NAME?”
=LEFT(A11;10) give the message “NAME?” (the same…)
Is there any solution for me?
hyperlinks.ods (26.7 KB) (spreadsheet with database range “DBRange” and some formulas generating hyperlinks to pictures)
Put this in the same directory as my database and pictures.
Now I have a number of pictures of faces, a file Database_FB.odb and another file hyperlinks.ods in the same directory (Headshots).
Database_FB.odb - a good example of how a database can be used.
What am I expected to see / do in the last file (hyperlinks.ods)?
I can’t see any images? (only the correct path - J3 and filename and sheet in J1)
In my case, I have no control over what I received - a document in .xlsx format.
And I want, if possible, not to buy MS Excel for this. 
You asked for clickable links. My formulas create clickable links.
I have resigned myself to having Excel for stuff like this.
Unfortunately, this function of Excel is used in quite a few business reports that I have received, and continue to receive, from various information providers, and so am faced with a similar problem.
It also means that you won’t be able to modify it in Calc and send it back to whoever first sent it to you without corrupting it, if ever that was your need.
Obviously, the import filter isn’t perfect, and this is one such instance.
In this case, the solution was as follows:
Now it became possible to carry out the previously described steps
And do this for all addresses (about 400)
In my case, I wrote a simple program in Autohotkey, which in any case performed each step of the sequence automatically.
The cell with the http address was highlighted and I pressed WinKey+n (and waited)
The program would have become more complicated if everything happened automatically.
#Requires Autohotkey 2.0
#SingleInstance Force
#n::
{ Send "{Ctrl down}c{Ctrl up}"
Send "{Left 7}"
Sleep 100
Send "!I"
Send "{Enter}"
Send "!n"
Send "{Tab}"
Send "{Ctrl down}V{Ctrl up}"
Send "!Ö"
Send "{Enter}"
Sleep 1000
Send "!B"
Send "{Enter}"
}
Since the images were of varying sizes, they could overlap. That meant I also wrote a small AHK program to handle the image sizes in Calc.
It was a bit more difficult to manage because the image had to be selected (not the cell the image was attached to)
#Requires Autohotkey 2.0
#SingleInstance Force
#n::
{ Click "Right"
Send "{Ctrl down}oo{Ctrl up}"
Send "{Enter}"
Sleep 100
Send "{Alt down}H{Alt up}2,5"
Send "{Alt down}O{Alt up}"
Send "{Up}"
}
In my case, I’m not interested in being able to “return” the images to the sender.
My wish remains, to either be able to see the image by clicking on a link
As this feature is not built into LO Calc (currently)
Are there only a few options. (as I see it)
Is it possible to do this through an API to Calc? Where is information about this? (have written programs that handle both text and cells in Calc - but not this)
To write a more automatic script, similar to what I did. (writing a macro is maybe possibly - but I feel unsure how to handle problems)
???
Switch to MS Office… (which I want to avoid - if possible)
Thank ypu! (Didn’t see which column was “clickable”)
Although these examples do not solve my desire in this case, two inspiring examples are supplied
(I think the database example is better)
What keeps me from database solutions, is that I haven’t found the tools (eg suitable APIs)
But that is another question.
The one with cell style “Hyperlink” (blue, underlined font).
Most of my samples do not contain a single line of macro code. Databases are prepared to deal with text and images.
Database_Calc.odb (1013.3 KB)
If you copy the attached database document into my “Headshots” directory, you see how Base can deal with spreadsheet data containing names of picture files. The document contains a form and a report.
Thank you! (Think it’s good to stay away from macros, if possible)
When I try to view the result from the latest database, I got an error message
No table with the name "DBRange
Sorry, I forgot that I modified something important in the spreadsheet. Open it, select the list range with the gray headers, call Data>Define and give name “DBRange” to the selection. Save, close, restart office suite. Now the database has the proper table references to that database range.
This thread any use?
I see someone created an extension:
Sorry - I don’t understand…
I open the database file Database_Calc.db but, what to do then?
Is the database DBRange? - under “Formulär” (on swedish) (form on english?)
If I open DBRange - under “Report” I got many errors.
No, open the spreadsheet instead, then continue as described in
Edit, save and close the spreadsheet. Or replace it with this one where I already made the change:
hyperlinks.ods (26.7 KB)