Can you get excel to auto hyperlink?

[Elliott_the_noob]

Hi everyone,

I have been tasked with building a log that will have hyperlinks built in. Currently my log has over 400 entries and will soon have well over 1000 by the end of the week. Each entry is information related to a test report that is saved on our server. Each of these test reports have a name that matches the information in my log so the matching part should be straight forward, however they are spread out over multiple folders (yet all those folders are contained within 1 master folder).

Is there a way to use a function, formula, or possibly a VBA to get excel to search for the criteria in my log and automatically create a hyperlink for each item?

This will help me out immensely.

Thanks

[kev_]

Yes - can be done with VBA

Are we going down only one layer to sub-folders from the master folder?
For one item:
- what is the value in your log?
- what is the file name including extension?
- is each item a single cell entry?
- is every other item consistent?

[Elliott_the_noob]

Ok so how the log is set up right now I have a formula in place to combine the information from column A(Work order)+B(Tag)+C(Description of part)+D(Heat #) which is what is used to save the file name. The file names are really long eg. >HEAT # XXX CAP, PIPE, 0.5IN, CS, A105N, 3000, THRD WO 12345678 TAG 08F00001. This log is to track material test reports and which work order they go to so a lot of info is included in the file name like work order, job tag, material info, etc.

In my log there is no sequential order to anything however the user can use drop down sort menus to find what they are looking for. Each test report is however numbered in sequential order as it is placed into the log.

On the server I have 1 master folder which has all the reports saved into it but it has subfolders broken up into the following:

MASTER FOLDER location: (P>196390>11.0>11.3.9 Documentation>
>Plant 05 A-E
>Plant 05 F-G
>Plant 05 etc etc
>Plant 08 A-E
>Plant 08 F-G
>Plant 08 etc etc
>Plant 10 A-E
and on and on and on......

[kev_]

Thanks.

I will have a look at this for you tomorrow

The code will be written in accordance with your answers - so be very precise to reduce your debugging issues

Q1 Does the log contain only one sheet? What is the name of sheet?

Q2 In which column is the report FileName?

Q3 Does the filename contain the extension?

Q4 Presumably all files have the same extension? (if not please give details). What is the extension?

Q5 Is the main folder..
"P:\196390\11.0\11.3.9 Documentation"

Q6 Are there any subfolders of the main folders that should be excluded?
(if not it would be more flexible for the future to target all subfolders)

Thanks

[Elliott_the_noob]

Thanks Kev_ I found a workaround using just the hyperlink formula