Automatically insert hyperlinks to list of file

Originally Posted by gsrai31

Hi there
I have a list of file names in a column A header “Audio Files”. The files listed in this column are stored in the same directory but in a different folder called “Audio”.

And there is another list of file names in column B “Image Files”. The files listed in this column are stored in the same directory but in a different folder called “Images”.

I would like to be able to automatically loop through these file names and insert a hyperlink to appropriate file where first 20 characters of the file names match with first 20 characters of the name of the files stored in these two folder. The number of files and file names change every day.

Can someone please help me with this? Let me know if doesn’t make sense, I can try to explain this with some sample spreadsheet.

Thanks in advance
gsrai

Hi gsrai,

Maybe try this:

What this will do is take the two folders and create hyperlinks to all the files in both folders and Place your AUDIO files in Col A and your Image files in Col B . You will need to change the path to where your files are located.

Place this code in ThisWorkbook

Please Login or Register  to view this content.

Then place this code below in a Module.

Please Login or Register  to view this content.

Let me know if you need further assistance.

Thank You, Mike

Jerry, thanks for this. I did manage link the file using hyperlink function. The reason I added this part to my ealier request for help was that to automate the complete process using one macro. Hopefully Mike or someone else should be able help with this. Thanks for looking into this for me.
regards, gsrai

Originally Posted by gsrai31

Jerry, thanks for this. I did manage link the file using hyperlink function. The reason I added this part to my ealier request for help was that to automate the complete process using one macro. Hopefully Mike or someone else should be able help with this. Thanks for looking into this for me.
regards, gsrai

Hi, I may just be missing what you want to do. However see if this works some what and we can go from there.
Anyway...
I changed your file and added the script. You will only need to change these 2 lines below in order to map them to your folders. This will place hyperlinks to each file that is listed in each of your 2 folders.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Excellent! I had a quick look, and it appears to do almost everything I wanted to achieve.

Couple of issues, each hyperlink I click on, I get a message saying “hyperlink can be harmful to your computer….etc, do you want to continue?” Can this be disabled somehow?

Link to Image files: Another issue, it’s probably my fault that I have not understood my data properly, a link to an image file needs to be to a file that has exactly the same name as it appears in column AG.

Link to Audio files: Now I also realise that correct audio files are not being linked as my assumption that first 20 characters of a file name are same. Actually, the link to an audio file will need to be to a file that has same name as column AG, up to the dot, and then add _1.wav. So if I were to put a link manually I will use this formula. =HYPERLINK(path&"\"&LEFT(AG2,(FIND(".",AG2,1))-1)&"_1.wav")

Also rather then changing the path to file in the code, can it be typed as values in two cells in the spreadsheet and code can automatically pick up images and audio folder names from these cell values.

Hope I have managed to explain this properly. Thank you again for your help.

Best Regards, gsrai

Hi Mike
Thank you for looking into this again for. The bit about having folder in B1 and B2 works fine now. The only issue is that it's not linking the correct image and audio files. These two bits need sorting out -

Link to Image files: a link to an image file needs to be to a file that has exactly the same name as it appears in column AG.

Link to Audio files: I realised that correct audio files are not being linked as my assumption that first 20 characters of a file name are same was not correct. Actually, the link to an audio file will need to be to a file that has same name as image file name in column AG, up to the dot before file extension, and then add _1.wav. So if I were to put a link manually I will use this formula. =HYPERLINK(path&"\"&LEFT(AG2,(FIND(".",AG2,1))-1)&"_1.wav")

Hope you or someone else be able to help me with these two issues.

Thank you for your help.

Best Regards
gsrai

Here you go, Lets see if we are any closer with this. Remember to follow the link in my other post to fix the issue with the error message when you open a link.

Keep me posted.

Thank You, Mike

Hi Mike

Perfect !! I had a quick look, and it appeared to work exaclty the way I wanted it to.
Thank you very much for your help with this.

Best Regards
gsrai

Hi Gsrai,


You are welcome,glad I could help so If everything is working correctly please mark your thread as Solved:

Here How...
Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.

If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

Thank You, Mike

Hi Mike, thank you for reminding me. Before I mark this thread solved, can you please help me with one more thing. The code you provided inserts hyperlink to complete path to each image and audio file. Is it possible to change the full path to "Link to Image" or "Link to Audio" text? thank you again.
Best Regards
gsrai

Hi,

Maybe this will do what you need.

Thank You, Mike

Hi Mike

This is exactly what I wanted. Thank you very much for all your help.
I will mark this thread solved now. Best wishes.

Regards
gsrai31