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?
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

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
45,918
Office Version
365
Platform
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!!!
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,404
Messages
5,511,156
Members
408,828
Latest member
Csmnvld

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top