multiple hyperlinks to external files. do I need a macro??

**smp** · 10-13-2008, 10:43 PM

Hi, I'm trying to hyperlink cells with specific data to external excel files, but have no idea how to do it (rather than do it one by one for 363 cells). Basically, the spreadsheet has data sorted by person's name and the month relevant to them, and I have external spreadsheets for each month of this year. What I want to do, is hyperlink the month name in the first spreadsheet to the external spreadsheet for that same month. Does this make sense??? I can do this one by one but since there is data for 363 people, it would take forever! So I was wondering if there was a macro that could do it automatically? Any help would be very much appreciated!

Thanks,
Sharn

**broro183** · 10-14-2008, 12:49 AM

hi Sharn,

Can you please attached a sample version of the file to show the structure & which includes an example of the filepath that needs to be linked?

Rob

**smp** · 10-14-2008, 01:36 AM

Hi Rob,

Here's a sample file that already has the months hyperlinked to external files. I've replaced the names due to confidentiality, but other than that, the file I'm working on is exactly the same. The external excel files that this spreadsheet is hyperlinked to are the same files that I need to link to the spreadsheet that I'm currently working on (only I'd prefer not to do it one by one for 363 people!). Let me know if you need any other info.

Thanks!
Sharn

**broro183** · 10-16-2008, 02:39 AM

hi Shaarn,

Try putting this code in a normal module & running it when you have one of the required sheets showing as the activesheet.

Please Login or Register  to view this content.

I use the Hyperlink formula because I find this is less likely to give problems over a network drive (compared to using [ctl + k] style hyperlinks.

This requires you to run it on each sheet that you need modified. but it could be changed to go through a defined range on each sheet if necessary - let us know...

hth
Rob

**smp** · 10-22-2008, 10:13 PM

Worked! Thanks Rob, your help is very much appreciated

**broro183** · 10-23-2008, 06:15 AM

Pleased I could help - thanks for the feedback :-)

Rob

**deisinger** · 02-15-2012, 05:20 PM

Rob, I have a similar issue. I am trying to create a notes field for each entry. I have created 500 note1-500 .txt. How can I create a hyperlink that I could drag that would increment the file name by 1? So instead of creating 500 separate hyperlinks, I could have a formula or macro that I could use instead?

**deisinger** · 02-15-2012, 05:26 PM

Rob, I have a similar issue. I am trying to create a notes field for each entry. I have created 500 note1-500 .txt. How can I create a hyperlink that I could drag that would increment the file name by 1? So instead of creating 500 separate hyperlinks, I could have a formula or macro that I could use instead?