| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell formula that captures name of the file's folder
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell formula that captures name of the file's folder
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell formula that captures name of the file's folder
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell formula that captures name of the file's folder
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell formula that captures name of the file's folder
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell formula that captures name of the file's folder
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" .(donotspam) wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell formula that captures name of the file's folder
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" .(donotspam) wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is this possible , screen captures with a macro? | Excel Worksheet Functions | |||
| How do I list a folder of "Excel file's names" to an Excel spdsht | Excel Discussion (Misc queries) | |||
| What formula captures specified rows of data? | Excel Worksheet Functions | |||
| Checking for Changes in File's Cell Layout | Excel Programming | |||
| Formula that captures criteria from 2 different columns "Help ME" | Excel Programming | |||
Hybrid Mode
