Automatically hyperlink to file without knowing extension

Hi Guys,

I have thousands of file of various types (pdf,xlsx,docx,dat etc) in a specific folder. This directory keeps growing as well some time i delete some files from it. Every file in the directory has a unique no. I have listed the file names in spreadsheet say sheet 1 column A. I want to create hyperlinks to all those file in the same sheet i.e. sheet 1 column B. If there is no file present in the directory with same name (it has been delete), correspondent cell in column B should return NA or "File Not Available"

I found two solutions to this problem at the following link. http://www.excelforum.com/excel-prog...extension.html

@AlphaFrog suggested both of these solutions a couple of years ago.

The first was

Please Login or Register  to view this content.

This prompts the user to choose folder where the files are residing and then creates hyperlinks in a new sheet.


The second code which looks more neat is as follows

Please Login or Register  to view this content.

This does exactly what i want but there are two small issues first it doesn't give the user option to select folder where the files are residing as i want yo keep this spreadhsheet in a different and second it only shows hyperlink in any cell column b if i activate column correspondent cell in column A i.e. either type the value and press enter or press F2 and then enter. I have more than 20,000 cell i want to avoid pressing enter each time i copy a value in column A.


So i was wondering is there any way where the second macro code can be amended where it gives me option to select the folder (Just like code 1) i.e. from where to create hyperlinks and avoid the necessity to press enter to generate hyperlink.

.
I tried to insert the macro code here but the Forum servers keep refusing because of specific symbols contained in the code.

You'll have to review the attached workbook.

My apologies.

This macro will create a hyperlink for each file in the folder. It does not rely on your list in Col A.
Hopefully you can add to the macro to finish your project.

Originally Posted by MarvinP

Hi,

I think this is a very easy problem if you use Power Query instead of VBA. Read:
https://www.thebiccountant.com/2017/...inks-to-excel/
http://efficiency365.com/2015/03/18/...iew-hyperlink/

Thanks for introducing me to Power Query. But it doesn't help as i want hyperlinks to local files in folder not to web urls. Beside i want hyper links to all the files which appear both in my naming list in column A and in folder. If there is a name in column A but there is no corresponding file in folder i want to skip it.

Thanks

Thanks for your reply

I already tried using

Please Login or Register  to view this content.

and then merging it with this

Please Login or Register  to view this content.

but due to almost zero knowledge of coding i don't know how to do it.

Your solution will only create a list of hyperlinks which i have to manually copy and past in-front of each file. In some cases i have 20,000 names in my list whereas there are only 15,000 files. so if i use your solution i have to manually find those 5,000 missing name and apply the hyperlinks to rest.

This is exactly what i need if it gives me option of choosing folder.

Please Login or Register  to view this content.