Cell formula that captures name of the file's folder

[Guest]

Hi,

How does one capture the folder name in which a file resides? I want to
ensure that the folder name (in this case a date) is always indicated in a
cell in a spreadsheet. I could always use a pop-up calendar that the user can
enact, but I want to force this date cell to always have the correct date
(not necessarily the current date), and this date will be the name of the
folder (e.g. "F0607_5FEB").

Thanks in advance,

 

[Guest]

Formulas at the bottom of this page

http://www.cpearson.com/excel/excelF.htm#SheetName

 

[Guest]

Thanks Tom. These formulas are interesting, but they don't return the
"foldername", just the filename or the full path. How would I adapt this
formula to return the foldername in which the filename resides?

Thanks.

 

[Gary Keramidas]

not sure what you're looking for, if the folder already exists or if you're
trying to create one.

this may give you the folder from which the workbook was opened.

Option Explicit
Sub test()
Dim fp As String
Dim fp2 As Variant
fp = ThisWorkbook.Path

fp2 = Split(fp, "\")
MsgBox fp2(UBound(fp2))
End Sub

 

[Guest]

based on the example you gave, i assumed by folder name that you meant the
tab name of the sheet. Nonetheless, you would just modify the formula to
get the folder name that the current file is saved in.

if the the cells("filename",A1) returns:

C:\AAAA_TOA\Toa_phase1\[Avenger_Stinger_DM.xls]Summary

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

returns the path
C:\AAAA_TOA\Toa_phase1\

 

[Guest]

Thanks. I decided to go another route.
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

based on the example you gave, i assumed by folder name that you meant the
tab name of the sheet. Nonetheless, you would just modify the formula to
get the folder name that the current file is saved in.

if the the cells("filename",A1) returns:

C:\AAAA_TOA\Toa_phase1\[Avenger_Stinger_DM.xls]Summary

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

returns the path
C:\AAAA_TOA\Toa_phase1\

--
Regards,
Tom Ogilvy

Thanks Tom. These formulas are interesting, but they don't return the
"foldername", just the filename or the full path. How would I adapt this
formula to return the foldername in which the filename resides?

Thanks.
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

 

[Guest]

Thanks Gary.