Insert File name in cell

[russelldt]

Hello,

I want to insert a file name in a cell when i open a file. This is what I am currently using:

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-5)

The -5 at the end removes the elm extension from the displayed name

This works when I open the file, directly from Finder. However, as the file is linked to a master file when I open this file using the link in the master file, I don't get the file name, It shows part of the directory structure.

Any help please

 

[mackc557]

Are Macros/VBA okay? If so you could try writing a function that uses Thisworkbook.Name and returns the value. Then you can use it as a function in the formula line, which should only care about what the file's name is.

 

[russelldt]

I am not up to scratch with macros- hence my attempt with this.

A macro would be very much appreciated. Thanks

 

[mackc557]

Go to your VBA editor, add a module to the VBA Project and paste:

Function FileName() As String
    FileName = ThisWorkbook.Name
End Function

Now you can go into your workbook, select a cell, and type =FileName()

 

[russelldt]

Go to your VBA editor, add a module to the VBA Project and paste:

Function FileName() As String
    FileName = ThisWorkbook.Name
End Function

Now you can go into your workbook, select a cell, and type =FileName()

Thanks very much. Such a simple solution.

 

[russelldt]

Thanks very much. Such a simple solution.

When i "Save As" the excel file, with a new File name, it retains the result from the original Filename(). Ie the original file name is xxx123, and when i save it as xxx124, the xxx123 appears in the cell in the xxx124 file.

 

[mackc557]

Fix for you:

Function FileName() As String
    Application.Volatile
    FileName = ThisWorkbook.Name
End Function

This should update after something changes on the sheet post name change.

 

[russelldt]

Fix for you:

Function FileName() As String
    Application.Volatile
    FileName = ThisWorkbook.Name
End Function

This should update after something changes on the sheet post name change.

Thanks, but still the same issue occurs.

 

[russelldt]

thanks , managed to get this to work - by deleting the initial script, and starting with the revised one you sent. I think i was running both at the same time.