how get filename with worksheet function?

I

Ian Elliott

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.
 
A

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
 
J

JMB

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)
 
A

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 )



JMB said:
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)

Ian Elliott said:
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.
Click to expand...
Click to expand...
 
J

JMB

I had assumed as much.

Arvi Laanemets said:
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 )



JMB said:
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)

Ian Elliott said:
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.
Click to expand...
Click to expand...
Click to expand...
 
H

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?
Click to expand...

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.
 
B

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!
 
B

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]...
 
Top