how get filename with worksheet function?

[Guest]

Thanks for any help.
I would like to get the filename with a worksheet function, is this possible?
I tried help "filename" and "file" but nothing of relation. What is a good
way to search for these things on the help file?
Thanks.

 

[Dave Peterson]

See Debra Dalgleish's site:
http://contextures.com/xlfaqFun.html#SheetName

I think I'd search google groups first. Then I'd learn enough to know what to
look for in excel's help.

 

[Arvi Laanemets]

Hi

=MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)-1)

The formula may be in any cell.
Instead A1 you can refer to any cell - am best you refer to same cell the
formula resides in.
NB! The workbook MUST be saved - in freschly created unsave workbook it
doesn't work!


Arvi Laanemets

 

[Guest]

Try

=Cell("Filename")

I think Arvi meant:
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

 

[Arvi Laanemets]

Hi

Thanks for correction. I put CELL function into A1 at start, and created an
expression to get file name from there - and of course I completly forgot to
replace the reference to A1 with formula :-(


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )

Try

=Cell("Filename")

I think Arvi meant:
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

Thanks for any help.
I would like to get the filename with a worksheet function, is this
possible?
I tried help "filename" and "file" but nothing of relation. What is a
good
way to search for these things on the help file?
Thanks.

 

[Guest]

I had assumed as much.

Hi

Thanks for correction. I put CELL function into A1 at start, and created an
expression to get file name from there - and of course I completly forgot to
replace the reference to A1 with formula :-(


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )

Try

=Cell("Filename")

I think Arvi meant:
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

Thanks for any help.
I would like to get the filename with a worksheet function, is this
possible?
I tried help "filename" and "file" but nothing of relation. What is a
good
way to search for these things on the help file?
Thanks.

 

[Harlan Grove]

Ian Elliott wrote...
....

I would like to get the filename with a worksheet function, is this possible?
I tried help "filename" and "file" but nothing of relation. What is a good
way to search for these things on the help file?

First, online help. You could always try looking up the term filename
in the help index. Actually, there's no entry for 'filename', but you'd
see 'file name' in the 'Or choose keywords' section of the help dialog.
The first topic for 'file name' is the CELL worksheet functions (at
least in XL10 (XP/2002)).

That said, CELL("Filename",A1) only returns the pathname if the file
has been saved. If you had other worksheets, you could use some
trickery. If you wanted the filename in a cell in worksheet A and you
also had a worksheet B, try

=MID(CELL("Address",B!$A$1),FIND("[",CELL("Address",B!$A$1))+1,
FIND("]",CELL("Address",B!$A$1))-FIND("[",CELL("Address",B!$A$1))-1)

This CELL("Address",Range) returns the workbook and worksheet names
along with the cell address when Range is in a different worksheet from
the cell calling the CELL function in its formula, EVEN IF THE FILE
HASN'T BEEN SAVED.

So one has to wonder why CELL("Filename",Range) returns "" for unsaved
files.

 

[Barry Clark]

This code
Code:
--------------------
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

--------------------
works almost perfect for my needs; however, I need it to drop the
extension to where only the name of the name of the file is left.

Any thoughts?

Thanks!

 

[Bob Phillips]

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND(".",CELL("file
name",A1))-FIND("[",CELL("filename",A1))-1)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Barry Clark" <[email protected]>
wrote in message
news:[email protected]...