Extracting path and filename from a formula with links

[Gavona]

I am selecting up to 20 cells containing formulae.
I want to identify if a formula involves a link to another workbook. If it does then I want to extract the filename and the path so that I can:
(i) test it exists
(ii)open it
before I copy the formulae to more cells
Thread707-1215569 tells a bit more of the context.

In Excel if the formula is in cell C2 then the filename can be extracted with:
=MID(C2,FIND("[",$C2)+1,FIND("]",$C2)-FIND("[",$C2)-1)

and the path with
=MID(C2,FIND("\",$C2)-2,FIND("[",$C2)-FIND("\",$C2)+2)

I guess the final solution will need to test for another link within the same cell to a different worksheet.

So I have the formulae in Excel but how do I do the equivalent in VBA? C2 would be replaced by a variable MyFormula
I guess I would put the results in
MyPath
MyFilename

Thanks,


Gavin

 

[Gavona]

Anyone?



Gavin

 

[PHV]

Have a look at the InStr function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886