Isolating file names in a formula

mikefromUK

New Member
Joined
Oct 15, 2002
Messages
42
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Forum statistics

Threads
1,144,329
Messages
5,723,733
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top