Adding Hyperlinks with VBA

Question

I have two columns in an Excel sheet containing Project names (Column A) and Fields (Column B) and I'm trying to add hyperlink for each row to local files. There's one specific local file for each row.

I don't know how to use Hyperlink function to get the corresponding URLs automatically because there are around 10,000 Project names. I also don't know if it is easier to use VBA.

The URLs are all identical except from "projects":

\nas1\backup\dop4\jobdata\projects\34s\34038 - 10 Wharaora Tce\Structural

The Project name in Column A is something like 34038, 25794 etc. and Filed in Column B is something like Structural, Civil etc.

So my plan is to write a VBA program that adds hyperlinks using URLs constructed from:

\nas1\backup\dop4\jobdata\projects

Because the Project name in column A is 5 digits, I'm thinking of using the LEFT function to get the first two digits to find the files after "Project file".

Folders
Excel file

Concatenate constant, column A and column B then use hyperlink in column D? — findwindow
If the project number is 32508 and the field is structural the file is stored in T/project/32's/32508/structural . so after clicking file "project" it will show like 21s, 22s,.... till 34s, then when i click each of them, the full Project name is then shown. — C.Sophia
I don't really understand how your data looks so can't help further :/ — findwindow
I'm trying to understand but it's really confusing. 1) What's the logic behind this statement: "so after clicking file "project" it will show like 21s, 22s,.... till 34s, then when i click each of them, the full Project name is then shown" 2)What will trigger to show all that with just one click? — EEM
could you post a meaningful piece of your data with the expected URLs — EEM

teylyn teylyn · Accepted Answer · 2015-11-18T22:38:17

A data sample will certainly help to understand what you are trying to achieve. Please update your question and then leave a comment to notify people who are watching this thread.

As far as I understand your question, you want to concatenate several cells into a string that can then be used to construct a hyperlink. Consider this screenshot

the formula in cell C2 is

="\nas1\backup\dop4\jobdata\projects\"&LEFT(TEXT(A2,"0"),2)&"s"

Copy down. In D2 you can use the Hyperlink() function to refer to the Address in C2, or wrap a hyperlink function around the formula in C2.

Adding Hyperlinks with VBA

1 Answers