Hyperlink to folders

[hoyyqs012]

Hi.

I have an excel 2007 sheet called "SERIAL.xlsx" located on C:\Docs, with two columns. One column contains (for each row) a serial number, and the other shall contain a link to folder whose name begins with this number, but also a title.

Example:

A B
1 000123 (Link to "C:\Docs\Stuff\000123 Foo")
2 000124 (Link to "C:\Docs\Stuff\000124 Bar")

I need a macro that makes this link. Example: It gets the 000123 number from column A, looks in the C:\Docs\Stuff folder for a folder that _begins_ with 000123, and then inserts a hyperlink in column B to this folder "C:\Docs\Stuff\000123 Foo". There will only be one folder in C:\Docs\Stuff for each number.

A bonus feature would be if the first part (C:\Docs) of the folder path in the hyperlink could be omitted, so that only the path from the folder where the document "SERIAL.xlsx" is located, is used (Stuff\000123 Foo). This would allow moving the document and folder system to different drives.

Any ideas? All help is very much appreciated! Thanks in advance.

[DonkeyOte]

Not clear but you could potentially use the HYPERLINK function rather than VBA?

B1:
=HYPERLINK(LEFT(CELL("filename";A1);FIND("[";CELL("filename";A1))-1)&A1;A1)

Not ideal but just a suggestion if you're looking to keep things as simple as possible.

(above working off assumption that file has been saved of course... else filename will return blank)