Macro to put hyperlink to an external file?

Paul, if

a) the filename of the part drawing is the same as the part number returned on your sheet (excluding extension obviously)

and

b) the drawings are all stored in the same folder

you should be able to create the link via a formula

Assuming your part was in H9 and was an .xls type file and was stored in C:\ you could use something like:

=HYPERLINK("[file:///c:\"&H9&".xls]Sheet1!A1",H9)

Obviously the above would need to be tweaked according to your requirements but that's the basic premise.

I would advise only one hyperlink per file, having multiple hyperlinks to different sheets in the same file is not something I'd advocate personally -- I'd let user determine which sheet to look in once file activated. If you wanted to merely open the file rather than navigate to a specific sheet (safer in case sheet names are inconsistent amongst files) then you could use:

=HYPERLINK("file:///c:\"&H9&".xls",H9)

HTH

Hi Luke,

Couple of problems with this, The part number would be 100100200 and is no problem, the drawing may be named 0207.100100200.01, can I put any wild cards in so that as long as part of the drawing no is the part number, it will find it?
Second problem is that nearly alll the drawings are opened in Windows Picture and Fax viewer, when you right click a drawing and select properties, it says file type is 01. The formula doesn't seem to like this.

Thanks again for your help

Paul

Further to my previous post, I've set the formula to find an excel file which I know is there, and it says 'Can't open the file'

Regards
Paul

I don't think you will be able to use a formula based approach if the files you require are not named the same as your images... you won't be able to use a hyperlink with wildcards, you will need to utilise Dir in VBA (with wildcards) to look for appropriate file and create hyperlink.

Are all the files required in the same folder ? Will the ID appear in only one file name ? Is .01 the file extension of all the files ?

Yes all the drawings are in the same folder. Most of the fles are .01, some are .tif. some of the names will bring have several sheets, e.g. 100100200.01.01 and 100100200.02.01.

HTH
Regards
Paul