Filename

Copper Contributor

Hello 

 

I´m using the Cell(filename) function, and this is working. 

But I have 2 or more Excel sheets open, with the same formula but different file names. 

But when I work in one file, it changes the name in all other Cell(filename) functions, to the one I'm currently working in. 

Is there a way to fix this? 

8 Replies
Cell("Filename",CellRef) returns the current Workbook name,,, please share the file you are working with, will help us to examine.

@Rajesh_Sinha 

I tried this but this does not work. 

Better upload the workbook and mark the cell in which you have applied the formula and let me examine,,, also if possible show me the expected output.

@Rajesh_Sinha 

There is no problem when I rename and safe this file. 

But when I open 2 or more Excel files at the sam time, they all get the same name, instead of getting the name og the sheet they are used in. 

 

@MMourits 

 

I got the issue ,, what you have to do ,,if you want to DUPLICATE it ,,, use SAVE as and assign another name,, rather then RENAME it. 

 

And even you have RENAME it then also it works ,,,, what I have noticed when open the file cell has ### is problem with width,,, or you just select cell E1 press F2 and finish with Ctrl+Shift+Enter.

 

  • Check I've loaded files are 3,,, 1 is yours 2nd is after SAVED as 3 is RENAME ,, all are working.

 

  • In case if U open all 3 together and finds the issue ,,, just select cell E1 press F2 and finish with Ctrl+Shift+Enter.

Let me find out a smart solution the I'll return soon to U.

  •  

@Rajesh_Sinha 

 

Hello 

It is still doing the same, I opened all 3 files and safeed them.
But they stille show the same name, in the work sheet, but not the file name of the sheet the formula i used in. 

 
 

image.png

 

@Rajesh_Sinha 

Found it:) 

by adding A1 in the referene it refferes to the current sheet and not the aktive one. :) 

 

@MMourits 

 

This is the permanent solution. What you have to do is, use VBA macro for Workbook_Activate  event.

 

Private Sub Workbook_Activate()

Range("E1").FormulaArray = "= MID(CELL(""filename""),SEARCH(""["",CELL(""filename""))+1,SEARCH(""]"",CELL(""filename""))-SEARCH(""["",CELL(""filename""))-6)"

End Sub

 

How it works:

  1. Open the 1st file, and to open the VB editor, press ALT + F11 .
  2. As soon you get the editor, find This Workbook Icon on left side in Project Explorer Windows.
  3. Click it.
  4. Copy & Paste above shown code.
  5. Press  ALT+Q to return to the Sheet.
  6. Now save the file as, MACRO ENABLED *.xlsm.
  7. Close the file.

Repeat step 1 to 7 with other file.

 

  • Now if you open all files together, and transfer control from one file to other, Excel will apply the Workbook_Activate event ,, and apply the array formula in cell E1 ,, and in this way STOP using the old file name.

Check the attached file for the code.