I need a method for comparing two file paths and removing the common root.
Specifically, I want to create a relative path from one file to another.

I do not know in advance what the file path is or how long it is. The files
may be of different types. A typical example might be:

Path1 = S:\Workgroups\Engineering\Graphics\Pictures\Overvi ew.bmp

Path2 = S:\Workgroups\Engineering\Trends\Spreadsheet.xls

Path3 = S:\Workgroups\Engineering\Graphics\Pictures\Sheets \Report.doc

I want to set Path1 to be the "home" location. The relative path for Path1
in my example would be:

.\Overview.bmp

The relative path from Path1 to Path2 would be:

...\Trends\Spreadsheet.xls

The relative path from Path1 to Path3 would be:

.\Sheets\Report.doc


First, I need to identify the common root. In my example, the common root is:

S:\Workgroups\Engineering\

Second, I need to replace my "Home" path path with ".\filename.ext"
In my example, I need to change my "Home" path to ".\Overview.bmp"

Third, I need to know if the path being compared is in a folder up or down
from the "home" file.

Replace EACH FOLDER ONE LEVEL UP from my "home" file with a "."

I need to replace EACH FOLDER ONE LEVEL DOWN from my "home" file -or- from
the common root with the name of the folder.

I have played with various versions of the split function, LEN(), INSTR(),
INSTREV(), LEFT(), RIGHT(), MID(), vbTextCompare, etc. without success. I
can get the full file and path name without a problem an create the "Home"
file name. It's the comparison that is tripping me up.

Any help you could provide would be greatly appreciated.

Susan Forson