I needed a macro to determine the relative path to a file I want to open. I wanted to store the relative path in a cell. Later I run another macro to read this relative path, open the file and read data. So this macro is just to help me fill in this cell for later use. I hacked this together and I expect there are more elegant ways, I am a real beginner Excel macro writer. If anyone wants they could write a better method. There might be flaws in my method, but so far it is working for me. All my relative files are in a subdirectory of the folder holding the macro that I am running.

I looked around the internet for a solution and did not find one, so I hope by posting others will find this example.

I take the current workbook and get its full and short name, and their lengths. The difference in lengths is the number of characters in the path to this workbook.

I then use an open window call to point to the file who's relative path I want to extract (I need its length as well). Then subtract the difference number above from the file length of the "opened" file, this is the number of characters to keep from the opened file name. Now I use the right command to keep only the characters I want. I add the two .. before the \ to indicate a relative reference.

Please Login or Register  to view this content.