Find a file folder name vba

[Alpacino]

Hi I have a macro that extracts info from another file on my computer.
What I would like to do is share this workbook with my collegues on other computers. Problem I have is that the other users put the file where I get the info from is kept in different folders in their own computers. Eg on own it's in mydocuments\Alan's workfolder\figures.xls other user would be something like mydocuments\finance\figures.xls

My macro needs to know where the file is located. Does anyone know to find the folder name this file is in??

Thanks
Alan

 

[Jonmo1]

Can your users at least be instructed to save both the Excel Doc and this other file in the SAME folder?

If that is reasonable, then you can use

ThisWorkbook.Path

 

[Alpacino]

I could do. Would it matter what the folder is called then in each user. They would probably save in the same folder anyway.

 

[Jonmo1]

No, it wouldn't matter...

As long as BOTH the file holding the macro, and the file the macro extracts data from are saved in the SAME folder, then ThisWorkbook.Path will work.