is there a "File name" function in ms Excel (2002)

[Shiperton Henethe]

Hi

Anyone know of any msExcel function that will
give the name of the current file?

To be honest I want if for printout purposes
because I am trying to avoid the custom header
etc (which seems difficult/impossible to format)



Shiphen

Shiperton Henethe

 

[Peo Sjoblom]

One way

=LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255),FIND(".",
MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255))+3)

can be put in any cell

if you want the full path

=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[",""
)

 

[RWN]

Peo has given you a solution, but, just for my own interest, what is so
difficult about formatting the filename in a header/footer?

 

[Shiperton Henethe]

Incredibly complicated but yes this does seems to work!

With thanks.


Ship

P.S. The *reason* why I wanted this is
it is that I want to be able to have my printouts say very distretely what
the file
name is: i.e. it's there but you have to look for it!
(So I've formatted it to size=6 and colour=mid grey!)

I strongly request that in future microsoft allow
the headers and footer to be formatted - and that
would save us all a lot of hassle!

It would also be nice to have a filename() function!

<end>

One way

=LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255),FIND(".",
MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255))+3)

can be put in any cell

if you want the full path

=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[",""
)

--

Regards,

Peo Sjoblom

Hi

Anyone know of any msExcel function that will
give the name of the current file?

To be honest I want if for printout purposes
because I am trying to avoid the custom header
etc (which seems difficult/impossible to format)



Shiphen

Shiperton Henethe

 

[RWN]

You can (in Xl97 & 2000, at least) display the filename using
=Cell("Filename") (include the quotes around "Filename")


--
Regards;
Rob
------------------------------------------------------------------------

Incredibly complicated but yes this does seems to work!

With thanks.


Ship

P.S. The *reason* why I wanted this is
it is that I want to be able to have my printouts say very distretely what
the file
name is: i.e. it's there but you have to look for it!
(So I've formatted it to size=6 and colour=mid grey!)

I strongly request that in future microsoft allow
the headers and footer to be formatted - and that
would save us all a lot of hassle!

It would also be nice to have a filename() function!

<end>

One way

=LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255),FIND(
".",

MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255))+3)

can be put in any cell

if you want the full path

=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[
",""

)

--

Regards,

Peo Sjoblom

 

[Dave Peterson]

it's probably best to add a cell reference to that formula:

=cell("filename",a1)

Otherwise, you might not get what you expect.

You can (in Xl97 & 2000, at least) display the filename using
=Cell("Filename") (include the quotes around "Filename")

--
Regards;
Rob
------------------------------------------------------------------------

Incredibly complicated but yes this does seems to work!

With thanks.


Ship

P.S. The *reason* why I wanted this is
it is that I want to be able to have my printouts say very distretely what
the file
name is: i.e. it's there but you have to look for it!
(So I've formatted it to size=6 and colour=mid grey!)

I strongly request that in future microsoft allow
the headers and footer to be formatted - and that
would save us all a lot of hassle!

It would also be nice to have a filename() function!

<end>

One way

=LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255),FIND(
".",

MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255))+3)

can be put in any cell

if you want the full path

=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[
",""

)

--

Regards,

Peo Sjoblom



Hi

Anyone know of any msExcel function that will
give the name of the current file?

To be honest I want if for printout purposes
because I am trying to avoid the custom header
etc (which seems difficult/impossible to format)



Shiphen

Shiperton Henethe

 

[RWN]

Dave;
I'm probably over my head here, but I don't understand the cell
reference.
When I use =Cell("filename") I get the path, workbook & worksheet name.
I'd appreciate it if you would enlighten me on what the reference
protects you from (I've found that a little bit of knowledge is indeed
dangerous).
--
Regards;
Rob
------------------------------------------------------------------------

it's probably best to add a cell reference to that formula:

=cell("filename",a1)

Otherwise, you might not get what you expect.

You can (in Xl97 & 2000, at least) display the filename using
=Cell("Filename") (include the quotes around "Filename")

--

Incredibly complicated but yes this does seems to work!

With thanks.


Ship

P.S. The *reason* why I wanted this is
it is that I want to be able to have my printouts say very

distretely
what

the file
name is: i.e. it's there but you have to look for it!
(So I've formatted it to size=6 and colour=mid grey!)

I strongly request that in future microsoft allow
the headers and footer to be formatted - and that
would save us all a lot of hassle!

It would also be nice to have a filename() function!

<end>










One way

=LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255),FIND(
".",

MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255))+3)

can be put in any cell

if you want the full path

=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[
",""

)

--

Regards,

Peo Sjoblom



Hi

Anyone know of any msExcel function that will
give the name of the current file?

To be honest I want if for printout purposes
because I am trying to avoid the custom header
etc (which seems difficult/impossible to format)



Shiphen

Shiperton Henethe

 

[J.E. McGimpsey]

Without the reference, the function returns the name of the sheet
last calculated, which may or may not be the sheet that the function
is on. Using the reference forces the function to evaluate the name
of the sheet that the reference is on - in this case, the same sheet
the function resides in.

 

[BrianB]

It does not protect you from anything.
It is just a way to show the file name on a worksheet so, for example,
this appears when the sheet is printed.

We use this extensively here because we produce certain workbooks on a
daily basis. We include the date/version number in the file name. One
in a cell you can also extract information using string functions such
as =MID().

 

[Dave Peterson]

Just so you can see it:

Create a new workbook with 2 sheets.

Save it.

Window|new window
window|arrange horizontal

Put the formula
=cell("filename")
in A1 of each sheet.

Try it with
=cell("filename",a1)

You'll see the difference.

Dave;
I'm probably over my head here, but I don't understand the cell
reference.
When I use =Cell("filename") I get the path, workbook & worksheet name.
I'd appreciate it if you would enlighten me on what the reference
protects you from (I've found that a little bit of knowledge is indeed
dangerous).
--
Regards;
Rob
------------------------------------------------------------------------

it's probably best to add a cell reference to that formula:

=cell("filename",a1)

Otherwise, you might not get what you expect.

You can (in Xl97 & 2000, at least) display the filename using
=Cell("Filename") (include the quotes around "Filename")

--

Incredibly complicated but yes this does seems to work!

With thanks.


Ship

P.S. The *reason* why I wanted this is
it is that I want to be able to have my printouts say very distretely
what
the file
name is: i.e. it's there but you have to look for it!
(So I've formatted it to size=6 and colour=mid grey!)

I strongly request that in future microsoft allow
the headers and footer to be formatted - and that
would save us all a lot of hassle!

It would also be nice to have a filename() function!

<end>










One way



=LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255),FIND(
".",
MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255))+3)

can be put in any cell

if you want the full path



=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[
",""
)

--

Regards,

Peo Sjoblom



Hi

Anyone know of any msExcel function that will
give the name of the current file?

To be honest I want if for printout purposes
because I am trying to avoid the custom header
etc (which seems difficult/impossible to format)



Shiphen

Shiperton Henethe

 

[RWN]

Got it,
Thanks, I'm a better person now.

--
Regards;
Rob
------------------------------------------------------------------------

Just so you can see it:

Create a new workbook with 2 sheets.

Save it.

Window|new window
window|arrange horizontal

Put the formula
=cell("filename")
in A1 of each sheet.

Try it with
=cell("filename",a1)

You'll see the difference.

Dave;
I'm probably over my head here, but I don't understand the cell
reference.
When I use =Cell("filename") I get the path, workbook & worksheet name.
I'd appreciate it if you would enlighten me on what the reference
protects you from (I've found that a little bit of knowledge is indeed
dangerous).

--

it's probably best to add a cell reference to that formula:

=cell("filename",a1)

Otherwise, you might not get what you expect.

RWN wrote:

You can (in Xl97 & 2000, at least) display the filename using
=Cell("Filename") (include the quotes around "Filename")

----------------------------------------------------------------------

--

Incredibly complicated but yes this does seems to work!

With thanks.


Ship

P.S. The *reason* why I wanted this is
it is that I want to be able to have my printouts say very distretely
what
the file
name is: i.e. it's there but you have to look for it!
(So I've formatted it to size=6 and colour=mid grey!)

I strongly request that in future microsoft allow
the headers and footer to be formatted - and that
would save us all a lot of hassle!

It would also be nice to have a filename() function!

<end>










One way

=LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255),FIND(

".",
MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255))+3)

can be put in any cell

if you want the full path

=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[

",""
)

--

Regards,

Peo Sjoblom



Hi

Anyone know of any msExcel function that will
give the name of the current file?

To be honest I want if for printout purposes
because I am trying to avoid the custom header
etc (which seems difficult/impossible to format)



Shiphen

Shiperton Henethe

 

[Shiperton Henethe]

As far as I can tell, it cant be done!
Not in Excel2002/Win2K it cant!
(At least if it can I'd love to know how.)


Ship
Shiperton Henethe

 

[Shiperton Henethe]

Crud!
I get exactly the same result whichever version I use.
(Excel2002/Win2K)

e.g. "D:\Docs\[TIMESHEET.xls]Summary"
As the previous poster said earlier in this thread
you have to strip out the directory and worksheet info
manually - which is rather messy to code.

What a friggin nightmare!
Thanks Micro$oft. (Not)


Ship
Shiperton Henethe

Just so you can see it:

Create a new workbook with 2 sheets.

Save it.

Window|new window
window|arrange horizontal

Put the formula
=cell("filename")
in A1 of each sheet.

Try it with
=cell("filename",a1)

You'll see the difference.

Dave;
I'm probably over my head here, but I don't understand the cell
reference.
When I use =Cell("filename") I get the path, workbook & worksheet name.
I'd appreciate it if you would enlighten me on what the reference
protects you from (I've found that a little bit of knowledge is indeed
dangerous).
--
Regards;
Rob
------------------------------------------------------------------------

it's probably best to add a cell reference to that formula:

=cell("filename",a1)

Otherwise, you might not get what you expect.

RWN wrote:

You can (in Xl97 & 2000, at least) display the filename using
=Cell("Filename") (include the quotes around "Filename")

--
Regards;
Rob

---------------------------------------------------------------------- --

Incredibly complicated but yes this does seems to work!

With thanks.


Ship

P.S. The *reason* why I wanted this is
it is that I want to be able to have my printouts say very distretely
what
the file
name is: i.e. it's there but you have to look for it!
(So I've formatted it to size=6 and colour=mid grey!)

I strongly request that in future microsoft allow
the headers and footer to be formatted - and that
would save us all a lot of hassle!

It would also be nice to have a filename() function!

<end>










One way



=LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255),FIND(
".",
MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255))+3)

can be put in any cell

if you want the full path



=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[
",""
)

--

Regards,

Peo Sjoblom



Hi

Anyone know of any msExcel function that will
give the name of the current file?

To be honest I want if for printout purposes
because I am trying to avoid the custom header
etc (which seems difficult/impossible to format)



Shiphen

Shiperton Henethe

 

[Dave Peterson]

I missed a step in my example of how to see the difference. Make sure that you
have two different worksheets visible when you do it. And then calculate (F9)
the workbook. And inspect the results of those two cells. You'll see the
difference.

I'm not sure about others, but I've never typed this formula myself (from
scratch):

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

But I have kept a few posts that showed it. And I have a link to Debra
Dalgleish's site. She has it here:
http://www.contextures.com/xlfaqFun.html#SheetName

Crud!
I get exactly the same result whichever version I use.
(Excel2002/Win2K)

e.g. "D:\Docs\[TIMESHEET.xls]Summary"
As the previous poster said earlier in this thread
you have to strip out the directory and worksheet info
manually - which is rather messy to code.

What a friggin nightmare!
Thanks Micro$oft. (Not)

Ship
Shiperton Henethe

Just so you can see it:

Create a new workbook with 2 sheets.

Save it.

Window|new window
window|arrange horizontal

Put the formula
=cell("filename")
in A1 of each sheet.

Try it with
=cell("filename",a1)

You'll see the difference.

Dave;
I'm probably over my head here, but I don't understand the cell
reference.
When I use =Cell("filename") I get the path, workbook & worksheet name.
I'd appreciate it if you would enlighten me on what the reference
protects you from (I've found that a little bit of knowledge is indeed
dangerous).
--
Regards;
Rob
------------------------------------------------------------------------
it's probably best to add a cell reference to that formula:

=cell("filename",a1)

Otherwise, you might not get what you expect.

RWN wrote:

You can (in Xl97 & 2000, at least) display the filename using
=Cell("Filename") (include the quotes around "Filename")

--
Regards;
Rob

----------------------------------------------------------------------
--

Incredibly complicated but yes this does seems to work!

With thanks.


Ship

P.S. The *reason* why I wanted this is
it is that I want to be able to have my printouts say very
distretely
what
the file
name is: i.e. it's there but you have to look for it!
(So I've formatted it to size=6 and colour=mid grey!)

I strongly request that in future microsoft allow
the headers and footer to be formatted - and that
would save us all a lot of hassle!

It would also be nice to have a filename() function!

<end>










One way




=LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255),FIND(
".",
MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255))+3)

can be put in any cell

if you want the full path




=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[
",""
)

--

Regards,

Peo Sjoblom



Hi

Anyone know of any msExcel function that will
give the name of the current file?

To be honest I want if for printout purposes
because I am trying to avoid the custom header
etc (which seems difficult/impossible to format)



Shiphen

Shiperton Henethe

 

[Dave Peterson]

This was added in xl2002. You can put the path and filename into the footer.
(You can't change the color, but you can change the size of the font.)

File|page setup|header/footer
custom footer
click on the 6th button from the left.
You should see:
&[Path]&[File]
in the active section.

Highlight that
click the A icon (first icon on the left)

change the fontsize to what you want.

 

[Shiperton Henethe]

Ah-ha! That's more like it.
Just one tiny problem - you cant change the colour of the text.

Better, micro$oft... !

Alec

This was added in xl2002. You can put the path and filename into the footer.
(You can't change the color, but you can change the size of the font.)

File|page setup|header/footer
custom footer
click on the 6th button from the left.
You should see:
&[Path]&[File]
in the active section.

Highlight that
click the A icon (first icon on the left)

change the fontsize to what you want.

As far as I can tell, it cant be done!
Not in Excel2002/Win2K it cant!
(At least if it can I'd love to know how.)

Ship
Shiperton Henethe


------------------------------------------------------------------------

 

[Quandan - Marcel Kreijne]

Dave, Alec,

The function you mention does return the sheetname, not the filename.
To return the filename you can use a similar approach though:

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

Kind regards,

Marcel Kreijne
Quandan - Steunpunt voor spreadsheetgebruikers
(Quandan - dutch supportsite for spreadsheetusers)
www.quandan.nl

P.S.: Yes Dave, I do code these myself.

I missed a step in my example of how to see the difference. Make sure that you
have two different worksheets visible when you do it. And then calculate (F9)
the workbook. And inspect the results of those two cells. You'll see the
difference.

I'm not sure about others, but I've never typed this formula myself (from
scratch):

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

But I have kept a few posts that showed it. And I have a link to Debra
Dalgleish's site. She has it here:
http://www.contextures.com/xlfaqFun.html#SheetName

Crud!
I get exactly the same result whichever version I use.
(Excel2002/Win2K)

e.g. "D:\Docs\[TIMESHEET.xls]Summary"
As the previous poster said earlier in this thread
you have to strip out the directory and worksheet info
manually - which is rather messy to code.

What a friggin nightmare!
Thanks Micro$oft. (Not)

Ship
Shiperton Henethe

Just so you can see it:

Create a new workbook with 2 sheets.

Save it.

Window|new window
window|arrange horizontal

Put the formula
=cell("filename")
in A1 of each sheet.

Try it with
=cell("filename",a1)

You'll see the difference.

RWN wrote:

Dave;
I'm probably over my head here, but I don't understand the cell
reference.
When I use =Cell("filename") I get the path, workbook & worksheet name.
I'd appreciate it if you would enlighten me on what the reference
protects you from (I've found that a little bit of knowledge is indeed
dangerous).
--
Regards;
Rob
------------------------------------------------------------------------
it's probably best to add a cell reference to that formula:

=cell("filename",a1)

Otherwise, you might not get what you expect.

RWN wrote:

You can (in Xl97 & 2000, at least) display the filename using
=Cell("Filename") (include the quotes around "Filename")

--
Regards;
Rob

----------------------------------------------------------------------
--

Incredibly complicated but yes this does seems to work!

With thanks.


Ship

P.S. The *reason* why I wanted this is
it is that I want to be able to have my printouts say very
distretely
what
the file
name is: i.e. it's there but you have to look for it!
(So I've formatted it to size=6 and colour=mid grey!)

I strongly request that in future microsoft allow
the headers and footer to be formatted - and that
would save us all a lot of hassle!

It would also be nice to have a filename() function!

<end>










One way




=LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255),FIND(
".",
MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255))+3)

can be put in any cell

if you want the full path




=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[
",""
)

--

Regards,

Peo Sjoblom



Hi

Anyone know of any msExcel function that will
give the name of the current file?

To be honest I want if for printout purposes
because I am trying to avoid the custom header
etc (which seems difficult/impossible to format)



Shiphen

Shiperton Henethe

 

[Dave Peterson]

Yep. You're correct.

Do you do it more than once????
(I might save it for future use if I really needed it!)

Dave, Alec,

The function you mention does return the sheetname, not the filename.
To return the filename you can use a similar approach though:

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

Kind regards,

Marcel Kreijne
Quandan - Steunpunt voor spreadsheetgebruikers
(Quandan - dutch supportsite for spreadsheetusers)
www.quandan.nl

P.S.: Yes Dave, I do code these myself.

I missed a step in my example of how to see the difference. Make sure that you
have two different worksheets visible when you do it. And then calculate (F9)
the workbook. And inspect the results of those two cells. You'll see the
difference.

I'm not sure about others, but I've never typed this formula myself (from
scratch):

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

But I have kept a few posts that showed it. And I have a link to Debra
Dalgleish's site. She has it here:
http://www.contextures.com/xlfaqFun.html#SheetName

Crud!
I get exactly the same result whichever version I use.
(Excel2002/Win2K)

e.g. "D:\Docs\[TIMESHEET.xls]Summary"
As the previous poster said earlier in this thread
you have to strip out the directory and worksheet info
manually - which is rather messy to code.

What a friggin nightmare!
Thanks Micro$oft. (Not)

Ship
Shiperton Henethe

Just so you can see it:

Create a new workbook with 2 sheets.

Save it.

Window|new window
window|arrange horizontal

Put the formula
=cell("filename")
in A1 of each sheet.

Try it with
=cell("filename",a1)

You'll see the difference.

RWN wrote:

Dave;
I'm probably over my head here, but I don't understand the cell
reference.
When I use =Cell("filename") I get the path, workbook & worksheet name.
I'd appreciate it if you would enlighten me on what the reference
protects you from (I've found that a little bit of knowledge is indeed
dangerous).
--
Regards;
Rob
------------------------------------------------------------------------
it's probably best to add a cell reference to that formula:

=cell("filename",a1)

Otherwise, you might not get what you expect.

RWN wrote:

You can (in Xl97 & 2000, at least) display the filename using
=Cell("Filename") (include the quotes around "Filename")

--
Regards;
Rob

----------------------------------------------------------------------
--

Incredibly complicated but yes this does seems to work!

With thanks.


Ship

P.S. The *reason* why I wanted this is
it is that I want to be able to have my printouts say very
distretely
what
the file
name is: i.e. it's there but you have to look for it!
(So I've formatted it to size=6 and colour=mid grey!)

I strongly request that in future microsoft allow
the headers and footer to be formatted - and that
would save us all a lot of hassle!

It would also be nice to have a filename() function!

<end>










One way




=LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255),FIND(
".",
MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255))+3)

can be put in any cell

if you want the full path




=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[
",""
)

--

Regards,

Peo Sjoblom



Hi

Anyone know of any msExcel function that will
give the name of the current file?

To be honest I want if for printout purposes
because I am trying to avoid the custom header
etc (which seems difficult/impossible to format)



Shiphen

Shiperton Henethe

 

[J.E. McGimpsey]

If you do save it, here's a version that uses one less function call
and one less operation (I keep it in my functions library):

=MID(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),
FIND("[",CELL("filename",A1))+1,255)

 

[Quandan - Marcel Kreijne]

Dave,

Yes I do write the formulas as I need them... however for sheetnames I
prefer to use the following tiny little VBA code:

Function qdSheetName() As String
qdSheetName = Application.Caller.Parent.Name
End Function

Not because it works better, but just because it is much clearer for the
user to see what a function like =qdSheetName() might do
than guessing what the long formula below would do...

Kind regards,

Marcel Kreijne
Quandan - Steunpunt voor spreadsheetgebruikers
(Quandan - dutch supportsite for spreadsheetusers)
www.quandan.nl

Yep. You're correct.

Do you do it more than once????
(I might save it for future use if I really needed it!)

Dave, Alec,

The function you mention does return the sheetname, not the filename.
To return the filename you can use a similar approach though:

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

Kind regards,

Marcel Kreijne
Quandan - Steunpunt voor spreadsheetgebruikers
(Quandan - dutch supportsite for spreadsheetusers)
www.quandan.nl

P.S.: Yes Dave, I do code these myself.

I missed a step in my example of how to see the difference. Make sure that you
have two different worksheets visible when you do it. And then

calculate
(F9)

the workbook. And inspect the results of those two cells. You'll see the
difference.

I'm not sure about others, but I've never typed this formula myself (from
scratch):

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

But I have kept a few posts that showed it. And I have a link to Debra
Dalgleish's site. She has it here:
http://www.contextures.com/xlfaqFun.html#SheetName

Shiperton Henethe wrote:

Crud!
I get exactly the same result whichever version I use.
(Excel2002/Win2K)

e.g. "D:\Docs\[TIMESHEET.xls]Summary"
As the previous poster said earlier in this thread
you have to strip out the directory and worksheet info
manually - which is rather messy to code.

What a friggin nightmare!
Thanks Micro$oft. (Not)

Ship
Shiperton Henethe

Just so you can see it:

Create a new workbook with 2 sheets.

Save it.

Window|new window
window|arrange horizontal

Put the formula
=cell("filename")
in A1 of each sheet.

Try it with
=cell("filename",a1)

You'll see the difference.

RWN wrote:

Dave;
I'm probably over my head here, but I don't understand the cell
reference.
When I use =Cell("filename") I get the path, workbook &

worksheet
name.

I'd appreciate it if you would enlighten me on what the reference
protects you from (I've found that a little bit of knowledge is indeed

dangerous).

------------------------------------------------------------------------

it's probably best to add a cell reference to that formula:

=cell("filename",a1)

Otherwise, you might not get what you expect.

RWN wrote:

You can (in Xl97 & 2000, at least) display the filename using
=Cell("Filename") (include the quotes around "Filename")

--
Regards;
Rob


----------------------------------------------------------------------
--

Incredibly complicated but yes this does seems to work!

With thanks.


Ship

P.S. The *reason* why I wanted this is
it is that I want to be able to have my printouts say very
distretely
what
the file
name is: i.e. it's there but you have to look for it!
(So I've formatted it to size=6 and colour=mid grey!)

I strongly request that in future microsoft allow
the headers and footer to be formatted - and that
would save us all a lot of hassle!

It would also be nice to have a filename() function!

<end>










One way




=LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255),FIND(

".",

MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255))+3)

can be put in any cell

if you want the full path




=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[
",""
)

--

Regards,

Peo Sjoblom



Hi

Anyone know of any msExcel function that will
give the name of the current file?

To be honest I want if for printout purposes
because I am trying to avoid the custom header
etc (which seems difficult/impossible to format)



Shiphen

Shiperton Henethe