Automatically hyperlink to file without knowing extension

Hi guys,

I've spent the best part of the day tinkering with this, but just can't quite find the solution. Please, please can someone help!!

I'm trying to create a 'Document Management' spreadsheet which will essentially serve as an index of all files within the same folder. The users should be able log all files which are relevant to them, and hyper link straight from the spreadsheet to open each file.
I should probably mention at this point, that it's users will be relatively computer illiterate, therefore I need to require as little input from them as possible.

Column A will be manually inputted by the user, and will contain the file name of the relevant document, but not the extension. (i.e A1 = filename1, A2 = filename2 etc.)

The problem is that the files themselves are of different types (ie. pdf, word docs etc) therefore the formulae I came up with to automate the hyperlinks doesn't work.
To get it to function the user would also have to specify the file extension, i.e A1 = filename.doc, A2 = filename2.pdf.

I seriously doubt that they would be able to cope with this (sigh!) hence I need an alternative solution.

Can anyone advise as to whether there is some VBA which could help me achieve the same thing that I had intended with the formulae?

This is what I had put together, which produced a hyperlink in col B based on the file referred to in col A... (the 'isblank' was used to prevent errors in col B where no adjacent file had been listed in A)

=HYPERLINK(IF(ISBLANK(A2),"",LEFT(CELL("filename",B1),FIND("[",CELL("filename",B1),1)-1)),"View")

Any guidance gratefully received.

Laura

This will prompt the user to select a folder and then create a a hyper-linked file list of all the files within the selected folder. Would that work for you?

Please Login or Register  to view this content.

Hi AlphaFrog,

Afraid not - the hyperlinks need to be made in the background with no input from the user.
The workbook will always be stored in the same folder as the files which need to be linked - in VB speak - thisworkbook.path.

Could the code you provided be amended? I'm afraid I have no idea on how to structure VB.

Thanks
Laura

When the user makes an entry in column A, the code below will automatically look for a file with that name (any extension) in the Thisworkbook.Path. If found, a hyperlink is added to column B same row.

To install the code:

• Right-click on the worksheet tab
• Select View Code from the pop-up context menu
• Paste the code below in the VBA edit window

Please Login or Register  to view this content.

@oeldere - Apologies

@AlphaFrog,

Thanks so much, that works great.

I also found a workaround using a User-defined function... though I think I prefer your code for stability.

Please Login or Register  to view this content.

Alpha - one more thing. Is there anyway to have the code refreshed each time an amendment is made to the corresponding cell?

As it stands - if I delete the value in column A, the link in B remains.

Cheers

It could be done, but consider this, there is no way to make a workbook completely dummy proof. If your users can delete one cell but cannot figure out how to delete two cells, then you may want to up the bar (as it were) on who gets to use the software.

Oh AlphaFrog - if only it were that easy. Must get myself another job.... or failing that, colleagues!

On retrospect, I think I might be tempted to stick with the custom function. I'll lock the cells to prevent users from deleting the formulae!

I was wondering though, whilst on this topic, whether there would be any way to amend the UDF I'm using, to search within subfolders also? This would enable the files to have some form of organisation within the directory.

Sorry if this should be on a different thread - mods, please advise if this should be the case....

Here is the UDF once more:-

Please Login or Register  to view this content.