Macro to Automatically Link Cells to Files?

[hashbrown64]

Hey there,

So I'm not a VBA whiz by any means, and need some assistance with a problem I'm having.

I want to turn all of the cells in a column of my spreadsheet into links to the files they're referring to. For example, if the cell says "May 2008 Report", I want it to link to that file.

The good news is that there is a column right next to the name of the document that has a unique identifier with the file name. Basically, I need code to do the following:

Add a hyperlink to each cell to J:\CEP\DB-Allen-CD\AS2_2009\IMAGES\[A1]\[A2]\[A3].tif

So I need some code to automatically create the links using the data in columns 1, 2, and 3. The link would go in column 4. Anyone have any ideas?

 

[hashbrown64]

Another kink in the system - I'm going to have to add some words to the link, so it'll really look like this:

J:\CEP\DB-Allen-CD\AS2_2009\IMAGES\[A1]\000[A2]\DT 01-0[A3].tif

If I can automatically create that link (with the data from cells A1-A3 and so on filled in), and hook it up to the cells in [A4] (and so on), my life will be infinitely easier!

 

[Peter_SSs]

I'm a bit confused about your columns/rows. You mentuion the data in columns 1, 2, 3 but then refer to A1, A2, A3 which are all in one column.

Do you really need a macro? Could you adapt a formula like this in the cell where you want the hyperlink.?

=HYPERLINK("J:\CEP\DB-Allen-CD\AS2_2009\IMAGES\"&A1&"\000"&B1&"\DT 01-0"&C1&".tif")

 

[hashbrown64]

Sorry, it's difficult to explain. The data is contained in the same row as the output for the link, spread across 3 separate columns.

The problem with using that formula is I already have data in the row I'd like to add hyperlinks to - I want to keep that data, but add the hyperlink. So if cell A4 says "May 2008 Report," I want it now to say "May 2008 Report" with a link to the filepath generated by that formula.

Perhaps if I added a column to generate that hyperlink, could I automatically combine the link and the existing text somehow?

 

[hashbrown64]

Woo nevermind - I just discovered the second part of the hyperlink formula, which allows me to use the existing text as the display name! Thanks!!!