[Solved] Can't insert Cell value in Hyperlink

[Steve Z1000]

Hi Folks,

To organize my stock of Articles i made a Calc sheet with different Fields.
One of then is the article Nummber - Art.Num.
For all the Artiles i have a Picture with the same Name as the Art.Num. for Example art.Num 6700200345 and the Pic. is 6700200345.jpg

I want to add a Field with the Link to the Picture. As you can imagine for about 4000 Articles, adding this one by one is too much work.
Now i tried to insert the Art.Num. Cell value into the link.

For Example:

A1 is the Art.Num. Cell so i thought inserting A1 in the link would do C:\foulder\foulder\(A1).jpg

But this only gives me error.

Can anyone Help me with this Problem ?

Yet i didn`t work with Macros ! I`m still a Beginner !

[Villeroy]

Fun with hyperlinks

[Steve Z1000]

Thanxs but didn`t solve my Problem.

Maybe i explained it wrong.

The Link i try to create is that the Art.Num. is automaticly inserted in the Link.

[Villeroy]

If all this is on a Windows file system, the file names are in the form "012345.jpg" (no special characters nor spaces in the name) then your only problem is how to convert the Windows path ("C:\Documents and Settings\..." with spaces, backslashes and stuff) into a system independent file:///-URL.
Open my document and put =FileURL into a free cell.
That formula returns the file's URL wheras =FileURLPath returns the path without file name. This works since the document is derived from my default template which includes a set of named references.
Get some empty range in my document and call Insert>Names>Insert... and hit [Insert All]. This inserts a list of named references defined for this document.

Get your document and call Insert>Names>Define... [Ctrl+F3]
Define "cFileName" first, since the other names depend on this one, hit [Add] and then [OK].
Then define "FileURLPath" and the others if you like, hit [Add] for each of them and finally [OK].
If your pictures are in a subdirectory "Pics" of this document's path, you may add another name such as "PicPath" =FileURLPath&"Pics/"
A1: 012345.jpg
B1: some optional label or empty
=HYPERLINK(picPath&$A2;IF(LEN($B2)>0;$B2;$A2))
If you have a cell style (a named formatting) defined for hyperlinks like extensively used in my file, you may append &T(STYLE("Hyperlink")) to the above formula, where "Hyperlink" is a cell style defined in the stylist [key F11].

Yet i didn`t work with Macros ! I`m still a Beginner !

Macros are for lamers who do not understand the application they are hacking on.

Edit: Attached my test document with a directory "Comics". Extract the zip (./Comics and picture_archive.ods) and load the spreadsheet.

[Steve Z1000]

Thanx This solved my problem !