Obtain Path on open workbook

[ssGuru]

I need to obtain the path to a workbook when I open it and save it to
a named range "PathToForecastFile" in that workbook.

I have the code to obtain the filename of the current workbook:
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",
CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

I need to send out templates to clients and I cannot guarrantee they
will or can put it in a predefined path.

Thanks,
Dennis

 

[Bob Phillips]

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

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[ssGuru]

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

That gives me a #NAME? error Bob
Thanks, Dennis

 

[ssGuru]

Thanks, Bob,

My mistrake... I temporarily had the R1C1 set and that caused the
#Name error with the A1.

However =LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1)
results in a value of "C:\Data\SAP\[MT-ForecastRpt v2.070814.xls" and
I ONLY want the path and NOT the file name. This also results in a
path with an extra "[" bracket.

Close to what I neeed but looking just for the path to the file.

Thanks, Dennis

 

[ssGuru]

OK. This works
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)
Bracket needed to be reversed

Thanks Bob for getting me started in the right direction.

Dennis

 

[Bob Phillips]

I thought you wanted the book name as well.

Glad you are sorted.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)