Setting Worksheet Title = Filename = cell

Posted by Colin Fisher on December 21, 2000 3:59 PM

Is it possible to set the worksheet title to equal
the filename. I.E. if the file is called 8110.xls and
only has one worksheet in it, I want the worksheet
title to be called 8110. Then I want a cell, say
cell A1 to equal the worksheet name.

For what I'm doing the titles would be either 4 or
5 characters long, 6700.xls to 16000.xls

Thanks in advance.

Colin

Posted by Tim Francis-Wright on December 22, 2000 1:11 PM

The easiest way to do this is through a macro:
you can get the worksheet name into a cell
with a long formula (see http://www.cpearson.com/excel/excelF.htm#FileName),
but the VBA code is pretty tame:

IN Excel 97, I'd put this in the ThisWorkbook
object:

Private Sub Workbook_Open()
Dim AWN$

AWN = ActiveWorkbook.Name

If AWN Like "*.xl?" Then AWN = Left(AWN, Len(AWN) - 4)
Sheets(1).Name = AWN
Sheets(1).Range("A1").Value = AWN
End Sub

Posted by Neil Salvin on January 08, 2001 9:00 AM

Having read the comments regarding =filename on www.cpearson.com, is there a global setting I can use to change the appearance of the results of this formula. For example, I have a number of files created in Excel 5.0 which have a =filename formula showing as, say, c:\mydocs\myfile.xls, yet when viewed in Excel 97 this changes to c:\mydocs\[myfile.xls]Sheet1. HELP! I want my old style names back! Is there anything I can do other than opening each file individually and changing the formula to =SUBSTITUTE(SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",
CELL("filename",A1))),"[",""),"]","") ?

Posted by Tim Francis-Wright on January 13, 2001 5:36 AM

One option is to set up a VBA function that
generates a well-behaved filepath and filename;
you would need to have this in your PERSONAL.XLS
file. Even this won;t work well if several
users will be using the files at different times.