Macro to Automatically Link Cells to Files?

hashbrown64

New Member
Joined
Aug 6, 2010
Messages
4
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?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

hashbrown64

New Member
Joined
Aug 6, 2010
Messages
4
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,107
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 6, 2010
Messages
4
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

New Member
Joined
Aug 6, 2010
Messages
4
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!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,132,994
Messages
5,656,289
Members
418,292
Latest member
spd87

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top