Is there a formula that will return the filename to a cell in EXCEL 2

[squirtguns]

I would like to have cell A1 return the name of the current excel workbook (file).<br>
<br>
example= Filename is Monthlysales.xls<br>
<br>
I would like cell A1 = monthlysales.xls<br>
<br>
Thanks!

 

[Guest_imported]

No, there's no function, but here's a VBA macro that will do it:<br>
<br>
Sub InsertFilename()<br>
'<br>
' InsertFilename Macro<br>
' Macro recorded 3/24/00 by Lee Meinhardt<br>
'<br>
<br>
'<br>
Range(&quot;A1&quot.Select<br>
ActiveCell.FormulaR1C1 = ThisWorkbook.Name<br>
End Sub<br>
<br>
Just open the Visual Basic Editor (Tools Menu) and paste this into your macro sheet.<br>
<p> Lee<br><a href=mailto: lee.meinhardt@smna.com> lee.meinhardt@smna.com</a><br><a href= > </a><br>

 

[misterstick]

wouldn't a function be more useful?<br><br>Public Function ThisFileName() As String<br>&nbsp;&nbsp;ThisFileName = ActiveWorkbook.Name<br>End Function<br><br>then you can use it in any cell you like like any other function.<br>unfortunately this isn't volatile, so if the filename changes it won't automatically update, even if you press F9. you have to reenter the formula.<br><br>is there any way of making it recalculate?<br><br>mr s. &lt;

 

[dsi]

To force a recalculation, you can monitor the sheets activity. For example, if you want to recalculate any time something on the sheet is changed then:<br><br>Private Sub Worksheet_Change(ByVal Target As Excel.Range)<br>&nbsp;&nbsp;&nbsp;&nbsp;Range(&quot;A1&quot.Calculate<br>End Sub<br><br>If you want to calculate upon the sheets activation:<br><br>Private Sub Worksheet_Activate()<br>&nbsp;&nbsp;&nbsp;&nbsp;Range(&quot;A1&quot.Calculate<br>End Sub<br><br>You can also force the field to update without a function.<br><br>Private Sub Worksheet_Change(ByVal Target As Excel.Range)<br>&nbsp;&nbsp;&nbsp;&nbsp;Range(&quot;A1&quot.FormulaR1C1=ThisWorkbook.Name<br>End Sub<br><br>Any time the sheet is changed, the value in A1 will be updated.<br>

 

[Dreamboat]

Suppose our active sheet is named &quot;MySheet&quot; in the file C:\Files\MyBook.Xls.<br><br>To return the file name without the path, use <br><br>=MID(CELL(&quot;filename&quot;,A1),FIND(&quot;[&quot;,CELL(&quot;filename&quot;,A1))+1,FIND(&quot;]&quot;,<br>CELL(&quot;filename&quot;,A1))-FIND(&quot;[&quot;,CELL(&quot;filename&quot;,A1))-1)<br><br>This will return &quot;MyBook.xls&quot;<br><br>To return the file name with the path, use either<br><br>=LEFT(CELL(&quot;filename&quot;,A1),FIND(&quot;]&quot;,CELL(&quot;filename&quot;,A1))) Or <br><br>=SUBSTITUTE(SUBSTITUTE(LEFT(CELL(&quot;filename&quot;,A1),FIND(&quot;]&quot;,<br>CELL(&quot;filename&quot;,A1))),&quot;[&quot;,&quot;&quot,&quot;]&quot;,&quot;&quot<br><br>The first syntax will return &quot;C:\Files\[MyBook.xls]&quot;<br><br>The second syntax will return &quot;C:\Files\MyBook.xls&quot;<br><br>In all of the examples above, the A1 argument to the =CELL function forces Excel to get the sheet name from the sheet containing the formula.&nbsp;&nbsp;&nbsp;Without it, and Excel calculates the =CELL function when another sheet is active, the cell would contain the name of the active sheet, not the sheet actually containing the formula.<br><br>I don't take credit for this. I got it from <A HREF="

http://www.cpearson.com/excel/topic.htm"

TARGET="_new">

http://www.cpearson.com/excel/topic.htm</A>

 

[chartee]

=cell(&quot;FILENAME&quot will return the location as well as the file name.....ie D:\DATA\Excel\MYFILE\sheet1

 

[keithinuk]

Good thread - thank you !!!!!
Keith

In case I forget to say 'Thank you' I'll say it now - thank you for your help !!!

 

[xlbo]

<cough>
How do I show the sheet name / filename in a cell
faq68-2561

This FAQ has been around for a while now
</cough>

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question