Isolating file names in a formula

[mikefromUK]

Hello All

Say we have a directory structure as below:

c:dir1
dir2
dir3
dir4
file1.xls
file2.xls
dir5
file3.xls
dir6
file4.xls




files1..4 are linked to each other (but not in a manner that generates a circular reference)

Every time I start a new section of work, I copy from dir4/5 down and only the names of dir2 and dir3 change. These directory names typically have dates in them. The problem is that the path that is used in the links need to be updated. Currently, every spreadsheet in this directory is opened and the links updated manually (Thankfully I have enough seniority around here not to have to do this). There are numerous directories and about 700 spreadsheets.

I am writing a program to go through and check that, if I change the links, eg from dir2 to dir7 - see below, that the links do actually exist. Sometimes they dont. This a precursor to writng a program that actually does the changes.

c:dir1
dir7
dir3
dir4
file1.xls
file2.xls
dir5
file3.xls
dir6
file4.xls




Problem: Each formulas often have several links in them

eg =c:dir1dir2dir3dir4file1.xls![sheet1]a1 + c:dir1dir2dir3dir5file2.xls!(sheet2]a1

would become

c:dir1dir7dir3dir4file1.xls![sheet1]a1 + c:dir1dir2dir3dir5file2.xls!(sheet2]a1

What is the best way to isolate each link in the forumlas? I was thinking of string manipulation but the links could have just about any character before and after them.

I need to check if the actual sheet exists, ie check c:dir1dir7dir3dir5file2.xls!(sheet2] exists. Checking if just the file exists is not sufficient as sometimes workbooks exist but don't have the named spreadsheets.

Any thoughts on what is the best way to do this?

Cheers

MG