| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a filename into a cell
Hi
try one of the following formulas (note: the workbook has to be save before). Just use the formulas as they are shown (don't replace 'filename' with anything else) File path and file name: =CELL("filename",A1) File path only =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) File name only =MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CEL L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1) The sheet name =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("file name",A1),1)) -- Regards Frank Kabel Frankfurt, Germany davidoo2005 wrote: How can I obtain the name of a file as a string? For instance, in workbook C:\Documents and Settings\Me\Folder\file1.xls, I would like file1 to be the string. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Inserting a filename into a cell
Hi
use these formulas directly in your worksheet 8no VBA required). For a VBA solution simply use something like sub insert_name() with activesheet .range("A1").value=.name end with end sub -- Regards Frank Kabel Frankfurt, Germany davidoo2005 wrote: Hi, When I run a program with the codes below I get an error that says "Compile Error: Function or Sub not defined" that refers to the function "CELL(". "Frank Kabel" wrote: Hi try one of the following formulas (note: the workbook has to be save before). Just use the formulas as they are shown (don't replace 'filename' with anything else) File path and file name: =CELL("filename",A1) File path only =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) File name only =MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CEL L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1) The sheet name =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("file name",A1),1)) -- Regards Frank Kabel Frankfurt, Germany davidoo2005 wrote: How can I obtain the name of a file as a string? For instance, in workbook C:\Documents and Settings\Me\Folder\file1.xls, I would like file1 to be the string. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Inserting a filename into a cell
Hi
msgbox activeworkbook.name -- Regards Frank Kabel Frankfurt, Germany davidoo2005 wrote: I'm sorry, I think I phrased the question poorly. I have about 2000 spreadsheets each containing a single data table, and I am trying to compile averages. For each workbook, I take some averages and copy them into a separate workbook. I would like to label each average I copy into the separate workbook with the name of the file they came from. I am wondering if there is a function in VBA that obtains the file name of the ActiveWorkbook as a string. Thanks! "Frank Kabel" wrote: Hi use these formulas directly in your worksheet 8no VBA required). For a VBA solution simply use something like sub insert_name() with activesheet .range("A1").value=.name end with end sub -- Regards Frank Kabel Frankfurt, Germany davidoo2005 wrote: Hi, When I run a program with the codes below I get an error that says "Compile Error: Function or Sub not defined" that refers to the function "CELL(". "Frank Kabel" wrote: Hi try one of the following formulas (note: the workbook has to be save before). Just use the formulas as they are shown (don't replace 'filename' with anything else) File path and file name: =CELL("filename",A1) File path only =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) File name only =MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CEL L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1) The sheet name =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("file name",A1),1)) -- Regards Frank Kabel Frankfurt, Germany davidoo2005 wrote: How can I obtain the name of a file as a string? For instance, in workbook C:\Documents and Settings\Me\Folder\file1.xls, I would like file1 to be the string. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Inserting a filename into a cell
That displays the string, but how can I obtain that string value as a string variable?
"Frank Kabel" wrote: Hi msgbox activeworkbook.name -- Regards Frank Kabel Frankfurt, Germany davidoo2005 wrote: I'm sorry, I think I phrased the question poorly. I have about 2000 spreadsheets each containing a single data table, and I am trying to compile averages. For each workbook, I take some averages and copy them into a separate workbook. I would like to label each average I copy into the separate workbook with the name of the file they came from. I am wondering if there is a function in VBA that obtains the file name of the ActiveWorkbook as a string. Thanks! "Frank Kabel" wrote: Hi use these formulas directly in your worksheet 8no VBA required). For a VBA solution simply use something like sub insert_name() with activesheet .range("A1").value=.name end with end sub -- Regards Frank Kabel Frankfurt, Germany davidoo2005 wrote: Hi, When I run a program with the codes below I get an error that says "Compile Error: Function or Sub not defined" that refers to the function "CELL(". "Frank Kabel" wrote: Hi try one of the following formulas (note: the workbook has to be save before). Just use the formulas as they are shown (don't replace 'filename' with anything else) File path and file name: =CELL("filename",A1) File path only =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) File name only =MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CEL L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1) The sheet name =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("file name",A1),1)) -- Regards Frank Kabel Frankfurt, Germany davidoo2005 wrote: How can I obtain the name of a file as a string? For instance, in workbook C:\Documents and Settings\Me\Folder\file1.xls, I would like file1 to be the string. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Inserting a filename into a cell
Try something like
Dim S As String S = ActiveWorkbook.Name -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "davidoo2005" wrote in message ... That displays the string, but how can I obtain that string value as a string variable? "Frank Kabel" wrote: Hi msgbox activeworkbook.name -- Regards Frank Kabel Frankfurt, Germany davidoo2005 wrote: I'm sorry, I think I phrased the question poorly. I have about 2000 spreadsheets each containing a single data table, and I am trying to compile averages. For each workbook, I take some averages and copy them into a separate workbook. I would like to label each average I copy into the separate workbook with the name of the file they came from. I am wondering if there is a function in VBA that obtains the file name of the ActiveWorkbook as a string. Thanks! "Frank Kabel" wrote: Hi use these formulas directly in your worksheet 8no VBA required). For a VBA solution simply use something like sub insert_name() with activesheet .range("A1").value=.name end with end sub -- Regards Frank Kabel Frankfurt, Germany davidoo2005 wrote: Hi, When I run a program with the codes below I get an error that says "Compile Error: Function or Sub not defined" that refers to the function "CELL(". "Frank Kabel" wrote: Hi try one of the following formulas (note: the workbook has to be save before). Just use the formulas as they are shown (don't replace 'filename' with anything else) File path and file name: =CELL("filename",A1) File path only =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) File name only =MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND(" ]",CEL L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1) The sheet name =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL ("file name",A1),1)) -- Regards Frank Kabel Frankfurt, Germany davidoo2005 wrote: How can I obtain the name of a file as a string? For instance, in workbook C:\Documents and Settings\Me\Folder\file1.xls, I would like file1 to be the string. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Are there any NON-Visual Basic solutions for inserting a picture based a derived or called filename? | Excel Worksheet Functions | |||
| Cell("filename") doesn't update to new filename when do save as. | Excel Worksheet Functions | |||
| substitute the filename in a cell reference with a string in another cell. | Excel Discussion (Misc queries) | |||
| Filename in a cell | Excel Worksheet Functions | |||
| Inserting Date In Filename Using Macro | Excel Programming | |||
Linear Mode
