Eliminating filename from path in formula

[debbiesh]

Hello again.

I found a way to save a workbook2 to the same directory and folder of the workbook1 that called upon it. But to make that happen, I type the path into the cell tied to the macro (cell B90).
I'm using the following formula to auto-copy the path of workbook1 to a cell in workbook2 --

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

The way it copies into workbook 2 is --

G:\Tidewater Sales\River Road\97844 Sign Repair\Estimating\[97844 Sign Repair Estimate 2.xls]

I believe the brackets in the filename are what's giving me a 404 error. Can't seem to alter the formula with my limited knowledge.

Any ideas? I just want the directory and folder names to appear in the path. Thanks!

[Domski]

If you're using VBA just use:

Please Login or Register  to view this content.

Dom

[debbiesh]

Thanks for replying, Dom. I'm probably making it too complicated. I want the path of the first workbook to appear in a cell within its sheet1. I have a macro that will copy that cell contents into sheet1 of the new workbook2. But I don't want the file name to appear.

How can I use your code in workbook1 to make the path appear in a cell? Like I said, limited knowledge. lol

[Domski]

Just like this:

Please Login or Register  to view this content.

Dom

[debbiesh]

K, thanks for your help. I'll work on it.