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

  • Thread starter Thread starter Shiperton Henethe
  • Start date Start date
S

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
 
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),"[",""
)
 
Peo has given you a solution, but, just for my own interest, what is so
difficult about formatting the filename in a header/footer?
 
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>










Peo Sjoblom said:
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


Shiperton Henethe said:
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
Click to expand...
Click to expand...
 
You can (in Xl97 & 2000, at least) display the filename using
=Cell("Filename") (include the quotes around "Filename")


--
Regards;
Rob
------------------------------------------------------------------------
Shiperton Henethe said:
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>










Peo Sjoblom said:
One way
Click to expand...
=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
Click to expand...
=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[
",""
)

--

Regards,

Peo Sjoblom
Click to expand...
Click to expand...
 
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
------------------------------------------------------------------------
Shiperton Henethe said:
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>










Peo Sjoblom said:
One way
Click to expand...
=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
Click to expand...
=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
Click to expand...
Click to expand...
Click to expand...
 
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
------------------------------------------------------------------------
Dave Peterson said:
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")
Click to expand...
--
Shiperton Henethe said:
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
Click to expand...
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
Click to expand...
=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
Click to expand...
=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
Click to expand...
Click to expand...
Click to expand...
 
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.
 
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().
 
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
------------------------------------------------------------------------
Dave Peterson said:
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")
Click to expand...
--
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
Click to expand...
Click to expand...
Click to expand...
 
Got it,
Thanks, I'm a better person now.

--
Regards;
Rob
------------------------------------------------------------------------
Dave Peterson said:
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:-)).
Click to expand...
--
Dave Peterson said:
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")
Click to expand...
----------------------------------------------------------------------
--

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






Dave Peterson said:
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
------------------------------------------------------------------------
Dave Peterson said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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

Dave Peterson said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.
 
Ah-ha! That's more like it.
Just one tiny problem - you cant change the colour of the text.

Better, micro$oft... !

Alec



Dave Peterson said:
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 said:
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


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

Dave Peterson said:
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 said:
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

Dave Peterson said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.

Dave Peterson said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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)
 
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

Dave Peterson said:
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.

Dave Peterson said:
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
Click to expand...
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 &
Click to expand...
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:-)).
Click to expand...
------------------------------------------------------------------------
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(
".",
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top