Create Hyperlinks in Excel for 5000+ .jpg Files in Column B Based on Drawing Names in Column A

Hello,

I have a spreadsheet with a list of drawings in column A, e.g.
A1: AW-17.00.00
A2: AW-17.01.00
A3: AW-17.02.00 etc. ...

In the place where the EXCEL file is located I have a folder with scans of drawings. The drawing scans are named the same as in the spreadsheet, so
C: \\ Nowy_folder \ scans \ AW-17.00.00.jpg
C: \\ Nowy_folder \ scans \ AW-17.01.00.jpg
C: \\ Nowy_folder \ Scans \ AW-17.02.00.jpg etc. ...

In the spreadsheet in column B, he would like to create hyperlinks to drawings from the folder C: \\. Yes, I can do it by clicking each time: right mouse - insert hyperlink - and search for a file - ok. But I have these names and files about 5000, there is no tape insertion of hyperlinks?

Greetings.

You can use the HYPERLINK function, which has a text argument. In this case it would be:
where path is the file path text followed by a backslash or cell reference with such text.

Hello,

Thanks for the answer, but it doesn't work.

The thing is that in column B, he enters a magic reference to the drawing from the folder whose names coincide (those from Excel and those from the * .jpg file), then I grab the cell in column B and drag down, and the hyperlinks add themselves taking into account the names from column A and the folder with * .jpg files. Only that I have already given names and they are permanent, so there must be matching names from column A with the same names from the folder with * .jpg files).

Greetings.

Give an example file with an attachment on which you can test it.

Hello,

File SKANY_PASY - nr. Fig. e.g. PŁ-145.00, the Hyperlink column has a link to the file PŁ-145.00.jpg. Unfortunately, I have to add each hyperlink separately by finding the file in folders. The names of jpg drawings are always the same as those in column A. Is there a way to enter the formula of the first hyperlink then just drag it over the entire area and the hyperlinks will adapt themselves?

folder with jpg files I did not suck, you can create any, but it is important that the files it contains have the same name as the items in column A.

Greetings.

It works for me. I checked 4 drawings. The file extension must be added because it is not in the spreadsheet, the placeholder name should also be displayed.

kato007 wrote:

... Thanks for the reply in favor, but it doesn't work.
...
The point is that in column B, I enter the magic reference to the drawing from the folder ... then I grab the cell in column B and drag down, and the hyperlinks are added by themselves taking into account the names from column A and the folder with * .jpg files ...

'Something' a friend got entangled in a column ' B 'there are unique generic names assigned to a given scan - if the cells start to be dragged here, each subsequent one will have the content of the one from which it was started to' drag '.
From what I see here it follows that something does not work because the hyperlink gives the wrong path to the file. When it gets organized, it works as a colleague wrote Maciej Gonet

So let's sort the topic:

1. The whole 'project' is in some directory - let's assume it's a directory called ' SKANPROJEKT '
2. Let the catalog above be placed temporarily (for these considerations) in C: \ temp
3. So the path to it will be - C: \ temp \ Skanprojekt
4. In the catalog Skanprojekt there is an excel base file called ' SKANY_PASY.xlsx '
5. In the same catalog Skanprojekt except file SKANY_PASY.xlsx there is also a subdirectory called SCANS for individual scans of documents
6. Base file SKANY_PASY.xlsx on the tab PASY_1JK in the fourth column, i.e. D 'is to have hyperlinks to document scans for file names placed in individual cells of the column' AND '
7. Thus, the path for the hyperlink (in column D) to the given scan will be:
C: \ temp \ Skanprojekt \ scans \ Nazwa_pobrana_z_komórki_kolumny_A & rozszerzenie_pliku_z_katalogu_SKANY

8. Existingin the base file the hyperlinks don't work, " perhaps "because first they are made for files with extensions' .tif ', not ' .jpg ', and secondly, there is a reference to the directory " BELTS ', not ' SCANS '
tif.jpg Download (16.45 kB)
9. If all is sorted out, then the hyperlink works

10. However, from what you can see here, with such a large number of scans (5,000 or more), the formulas in the cells can, although they don't have to, slow down the Excel sheet quite significantly
11. Maybe time to make a friend kato007 he thought about two simple macro commands: one for creating hyperlinks and the other for removing them?
12. You only need to make a certain assumption that there is one only directory for the Excel base file and for a subdirectory (eg: Scans), where there are scans of documents with specifically one extension for all files (just for order) - or it finds there are some thematic subdirectories for scan files ...

Sample code "installing" a hyperlink in a given cell of a spreadsheet:

Hello,

Thanks for the info, should I enter it in VB? to excel cell?

If you type in VB then after adding paths where and access drops out error (attachment)

I am not too strong at Excel below the path to access setpoints in the formula:

'where' - M: \ archiwum_structure \! _ ARCHIV_SCANY \ AGGREGATE_SCANS.xlsx
'access' - M: \ archiwum_konstr \! _ ARCHIW_SCANY \ AGREGATY \ Szafa_A

ps so that everything is clear, I can create individual hyperlinks, but this is about adding them massively without tedious clicking.

Greetings.

Added after 1 [hours] 2 [minutes]:

attachment..

Ad 1

kato007 wrote:

... I can create individual hyperlinks, but it is about adding them without tedious clicking

In a colleague's solution Maciej Gonet there is no "tedious clicking" .... ... enter a specific formula into a single cell, and then copy that formula using the gripper located in the lower right corner of that cell, by double clicking. The formula will copy itself to the next break in the column entries.
Below is the formula that creates the hyperlink he used Maciej Gonet - in a slight modification regarding the content of the hyperlink displayed in the cell:

" Scans\ "=> Subdirectory with document scans
Attention here : This entry means that the formula refers to a directory that is located in ' same place 'what the base file in which the "Hyperlink" formula was entered, if it is otherwise, you must provide the entire path to the directory with document scans, e.g. " M: \ archiwum_konstr \! _ ARCHIW_SCANY \ " or " M: \ archiwum_konstr \! _ ARCHIW_SCANY \ POWER \ Szafa_A "
A2 => The name of the given scan is the same as the file name in the "Scans" directory, but without the file extension (".jpg", ".tif", etc.) - here it is " TUL-145.00 "
" .jpg "=> File extension of scanned documents

Above for cell " A2 "gives such a record =>" Scans \ TUL-145.00.jpg "
Second part of the pattern " A2 & "-" & B2 "gives such a record =>" PŁ-145.00 - Bevel cutter "

Colleague kato007 he insisted on creating 'object-oriented' hyperlinks through Ctrl + Alt + H , and here it is a sheet formula / function, much more convenient in this case than the above Alt + Ctrl + H.

I attach an example that is a "modification" of a colleague's solution Maciej Gonet with two variants of the formula / formula ' hyperlink ':
s_SKANY_PA...unkcja.xls Download (44 kB)
If you put the above file in a directory and create a subdirectory in this directory " Scans "in which there will be photos / scans of documents with extension," .jpg ", this hyperlink will work

The second option variant " hyperlink "is based on the file extension found in the column" E "- it gives such" flexibility "that each file may have a different extension and the function will work, only it will have to be manually defined, which with the number of files> as many as several dozen will certainly be tedious ... ...

Ad 2
Let a friend make his life easier and, with longer entries in the table headers cells, which entries are to be found one below the other, apply Alt + Enter instead of entering the cosmic number of spaces between records, e.g.


Ad 3

kato007 wrote:

Code: text

Log in, to see the code

... well ... no ... I don't mean that much ... ... it was about access, but access was described with vba syntax commands, such as:

Let a friend try with the functions and see what comes out of it for now.

Hello,

It works that's what it was about files in the folder must have the same name as in the spreadsheet.

I won't play VBA (maybe someday) that's absolutely enough

Thanks cordially gentlemen.
Have a nice day.

Added after 22 [minutes]:

.. and I still have a question, is it possible to transfer the names of e.g. 200 files directly to Excel cells? i.e. I have named * .jpg or * .tif and with one click they land in separate cells of the sheet.
It would be another simplification because I would not have to write the name of the drawing a second time.

Greetings.

kato007 wrote:

... I will not play VBA (maybe someday) what is absolutely enough ...

Sometimes must ...

kato007 wrote:

... I still have a question, is it possible to transfer names of e.g. 200 files directly to Excel cells? i.e. I have named * .jpg or * .tif and with one click they land in separate cells of the sheet.
It would be another simplification because I would not have to write the name of the drawing a second time ...

... as in this case ... in the cell " I1 "the full path to the directory with scan files should be given - the macro draws from the directory the names of all files of the type:" .jpg "," .jpeg "," .png "," .tif "," .tiff "- if there are 5 5,000 will contract 5,000
Nazwy_pl..w.xls Download (29.5 kB)

Hello,

Thanks cordially Lanzul. all my issues have been successfully solved, congratulations on knowledge.

Have a nice day,
Greetings.

ps the topic is not closing maybe something else will happen