Excel Path to different directory

[bakgerman]

Hi,

Lets say I have the following directory structure
--Main
-----DirA
-----DirB


And lets say in DirA I have A.xls and in DirB I have B.xls


When I am working in DirA I would like to reference data in DirB .. so currently it is done something like this:
DirA -> Cell A1 -> ='C:\Users\myname\DirB\Sheet1'!$A$2


The problem that I am having is that when I send this file to a co-worker with the same folder structure .. I am having a problem with the path: 'C:\Users\myname\DirB\Sheet1'!$A$2 ... where it should be 'C:\Users\HISname\DirB\Sheet1'!$A$2 so the links are not working


Is there a way that I can do relative paths?
something like this?
'..\DirB\Sheet1'!$A$2

 

[HackSlash]

You can get the path to the workbook that contains the code and make all your references relative to that:

Dim PWD As String 
PWD = Application.ThisWorkbook.Path

 

[HackSlash]

I just realized that you probably want a formula and not VBA.

Here is a formula to get that info in to a cell:

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)

 

[bakgerman]

Hi HackSlash,

Thank you for you help!! I couldn't get that to work. To be honest I will be looking up about 5000 or so cells so would VBA not be a better solution? I am looking at your code:

Can you please be a little more specific


Dim PWD As String
PWD = Application.ThisWorkbook.Path

 

[HackSlash]

I called my variable PWD because there is a Linux command "pwd" that is "print working directory". It shows you the full path to where you are standing in the file structure.

That is what PWD contains. A full path.

You can test this by making a program, stepping in to it and looking at the value of PWD.

This guide will help you understand your code as you go along:

Debugging VBA