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

  • Thread starter Thread starter Shiperton Henethe
  • Start date Start date
You may want to add
application.volatile
to your function.

It still may be one calculation behind, though.


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" <[email protected]> schreef in bericht
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:-)).
--
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...
 
A little more versatile:

Public Function SheetName(Optional ByVal rng As Range) As String
Application.Volatile
If rng Is Nothing Then Set rng = Application.Caller
SheetName = rng.Parent.Name
End Function

which allows you to track sheetnames from other sheet/books when
they change.
 
Ahum...

Now I make the same mistake myself ;-)

I meant: for filenames I prefer to use the following tiny little VBA code:

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

Oh well... you guys get the sheetname function as a bonus ;-)

By the way, the reason why I do not use ThisWorkbook.Name is
because the function above can be used from another file without any
trouble...
And... this function does not require you to save your workbook before
giving
the workbookname in return...

Happy X-mas all!

Kind regards,

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

Quandan - Marcel Kreijne said:
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!)
Click to expand...
=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" <[email protected]> schreef in bericht
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
Click to expand...
Click to expand...
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:-)).
--
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...
 
Back
Top