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,
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

Formulas at the bottom of this page

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

--
Regards,
Tom Ogilvy


"klysell" wrote:

> 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,
> --
> Kent Lysell
> Financial Consultant
> Ottawa, Ontario
> W: 613.948-9557

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


"Tom Ogilvy" wrote:

> Formulas at the bottom of this page
>
> http://www.cpearson.com/excel/excelF.htm#SheetName
>
> --
> Regards,
> Tom Ogilvy
>
>
> "klysell" wrote:
>
> > 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,
> > --
> > Kent Lysell
> > Financial Consultant
> > Ottawa, Ontario
> > W: 613.948-9557

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


--


Gary


"klysell" <(E-Mail Removed).(donotspam)> wrote in message
news:C73DFA88-85BA-441E-8D10-(E-Mail Removed)...
> 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,
> --
> 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




"klysell" wrote:

> 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
>
>
> "Tom Ogilvy" wrote:
>
> > Formulas at the bottom of this page
> >
> > http://www.cpearson.com/excel/excelF.htm#SheetName
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "klysell" wrote:
> >
> > > 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,
> > > --
> > > Kent Lysell
> > > Financial Consultant
> > > Ottawa, Ontario
> > > W: 613.948-9557

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


"Tom Ogilvy" wrote:

> 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
>
>
>
>
> "klysell" wrote:
>
> > 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
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > Formulas at the bottom of this page
> > >
> > > http://www.cpearson.com/excel/excelF.htm#SheetName
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "klysell" wrote:
> > >
> > > > 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,
> > > > --
> > > > Kent Lysell
> > > > Financial Consultant
> > > > Ottawa, Ontario
> > > > W: 613.948-9557

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


"Gary Keramidas" wrote:

> 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
>
>
> --
>
>
> Gary
>
>
> "klysell" <(E-Mail Removed).(donotspam)> wrote in message
> news:C73DFA88-85BA-441E-8D10-(E-Mail Removed)...
> > 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,
> > --
> > Kent Lysell
> > Financial Consultant
> > Ottawa, Ontario
> > W: 613.948-9557
>
>
>