update Excel File Name (Rename an Excel Sheet), and it auto updates a specific cell in the workbook

Brass Contributor

I do have a question, that I seem to be hitting a wall on.  Is it possible to update an Excel File Name (Rename an Excel Sheet), and it auto update a specific cell in the workbook?  Here is the next part of the question.  I need this program to be able to follow the file, regardless where it is saved.  Meaning, if update the file on my computer, but email it to someone, I want the formula dynamic.  I need it to follow the sheet NOT look for coding to “My documents”, or “My computer” to know what the cell name is.  And if someone changes the file name on their computer, after I sent it to them, it auto updates the cell to the new file name they name it.

20 Replies

@Budman36  It sounds like you want the filename in a cell.  The function CELL() offers that sort of.  CELL("filename") actually gives the full path/reference but with some additional work you can have it trim just the filename out:

=LET(path,CELL("filename"),start,SEARCH("[",path)+1,end,SEARCH("]",path),fn,MID(path,start,end-start),fn)

if you don't have the newest version of Excel with the LET() function you can use:

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

but maybe the full path would be even better for you?

 

@mtarler The MID works, but I am hitting a road block.   

 

Here is my example of a book name:

21MJNXXX. Customer Name. Job Name. REV 1

 

I need to separate each section into a cell of there own... 

21MJNXXX

Customer Name

Job Name 

REV 1

 

The "Text to Columns" function will not work because it shows the formula.  Not the actual text.  I have tried =LEFT, =LEFT(RIGHT, .... =LEFT(MID... but I can't seem to get this right?  Is it possible do the text to columns with code?

@Budman36 Yes you can use similar techniques to further divide the filename.  I assume a period (.) is between each part so do you want the full name in 1 cell and then break it down or only have the parts?

for example if the =MID() formula is in cell A1  then you could paste this in A2 and copy down:

=TRIM(MID(SUBSTITUTE($A$1,".",REPT(" ",LEN($A$1))),LEN($A$1)*(ROWS($A$1:A1)-1)+1,LEN($A$1)))

 

@Budman36 

As variant

=LET(txt, A1, delim, ".", nDelim, LEN(txt)-LEN(SUBSTITUTE(txt, delim,"")),
  ch, UNICHAR(9999),
  n, SEQUENCE(1,nDelim+1),
  nn, SEQUENCE(1,nDelim+1,2),
  startPos,IFERROR(FIND(ch,SUBSTITUTE(txt,delim,ch,n-1))+1,1),
  numChars, IFERROR(INDEX(startPos,nn)-1,LEN(txt)+1)-startPos,
  TRIM(MID(txt,startPos,numChars))
)

for

image.png

 

@mtarler This worked very well, on a local drive, personal computers, etc., but it doesn't work when uploaded to sharepoint?  Any ideas?

@Budman361530 

CELL("filename") is not implemented for Excel Online

even if I did, I don't know it would work.  file references in sharepoint are, let's say different. 

You originally said the problem was that you were e-mailing the file to people and they would save it on their computers and often in the process they would change the name.  If it is on sharepoint, why would the name change?  What is it that you need this name for?  For example, on the desktop you can just give an address to point to other files but in sharepoint due to it being a server and security things (and honestly I guess I don't really know the exact reasons) it isn't that easy.  But regardless you are asking for the name of itself, not some other file.  I still not clear why.

@mtarler I made an estimating program.  Each quote/job gets its own copy of this program in a unique folder.  In this folder we put all customer correspondence, vendor quotes, etc..  I make these folders (that contain these workbooks) 50 to 100 at a time.  They are all numbered consecutively, so I use a "Renaming Program" to do them in batches.  When you open the WB, I made a reference cell that looks for that unique number in the name and it auto populates the rest of the book.  95% of our work is done on a company shared drive, and the program works well.  The 5% (one specific sheet) needs to be updated real time by the field guys.  For this sheet alone, I use a Sharepoint with a link that I post on our on-line scheduling program.  That also works well.  In the past I just simply saved a sheet, and named it manually.   I was just trying to streamline the process.  95% of it works awesome.  Looks as if this 5% may not work.  

@Budman361530 

You may vote for this idea here CELL function added to online – Welcome to Excel’s Suggestion Box! (uservoice.com) 

As for the rest I agree with @mtarler , it all very depends on concrete scenario how do you work with file.

@Budman361530 

Option could be to work with synced by OneDrive folders, in this case CELL() returns URL which includes filename and you need to parse it by bit different way. Or use Office Script in Excel Online only version.

 

In both cases solutions won't be compatible with one for the network shared files.

@Budman36 @Budman361530 So if I am getting the gist of this it is something like: you have a whole package of files/forms and such you use to quote a job and let's say each year you create 100 of them for the year and each quote is named 2021-0001, 2021-0002, .... and so in each excel file you want relevant cells that refer to the quote to properly use the actual quote number. But you are using some batch file or something else to create 100 copies and rename them to all the "correct" names like "quote 2020-0001".

So can you make that program also change a cell value in the file?  If not then maybe you should make a macro in Excel VBA to create those 100 files and update a cell in each file accordingly.  That macro wouldn't be hard to make. 

@mtarler Yes you have the idea, but I do about 50 a month.  Each folder has multilabel folders, word docs, and an estimating program that has nine sheets.  Each sheet populates with information, and I use macro's to do a lot of the functions.   One of the nine sheets I need my Forman to have access to, but I don't want them involved with the rest of the sheets.  So I basically have that sheet as a stand alone sheet in each folder.   I tried to have that one sheet print or save as a stand alone Work Sheet/book, with a macro, but I couldn't get it to work.   

@Budman361530  Without more info on the sheets and file structure I'm in the dark.  If you tried before and couldn't get the macro to work, why didn't it work?  Why not post that sheet or at least the macro to this forum so someone can help you fix it and make it work.  Make sure to give all the information because it is hard for us to guess what you mean or want or intended.

@mtarler See your point.... I was trying to keep it simple, and I was making things more complicated.  Here is the the coding of my macro.   First I am trying to just get it to work, and I can't.  Next I was trying to get it to preform like another Macro I have, that works for saving different sheets in a PDF in whatever folder they are in, with whatever name the file is.   

 

First Try.... wont save as a Macro enabled WB. In "debug" it show the wb.saveas line as the issue.

 

Sub sb_Copy_Save_Worksheet_As_Workbook()
Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Sheets("Task Sheet.").Copy Before:=wb.Sheets(1)
wb.SaveAs "C:\Users\Budman361530\Documents\New folder\Tasksheet.xlsx"
End Sub

 

Second Try... wont allow me to save as an .xlsm

 

Sub sb_Copy_Save_Worksheet_As_Workbook()
Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Sheets("Task Sheet.").Copy Before:=wb.Sheets(1)
wb.SaveAs "C:\Users\Budman361530\Documents\New folder\Tasksheet.xlsm"
End Sub


Ulitmaly tyring to work as this code...., but saving as a Macro WB vs. a PDF.

 

Sub MacroContractQuote()

'
' Macro1 Macro
'
'
'ChDir _
'"Z:\Shared\Budman361530 Shared\Budman361530 Quote and Jobs\01 - Master Quote Folder Templet REV 1.1 DO NOT USE WORKING ON THIS"
'ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
'"Z:\Shared\Budman361530 Shared\Budman361530 Quote and Jobs\01 - Master Quote Folder Templet REV 1.1 DO NOT USE WORKING ON THIS\20MJNXXXX REV 1.1 - Copy.pdf" _
', Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
':=False, OpenAfterPublish:=False
Dim strPath As String
Dim strName As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
strPath = ThisWorkbook.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "/Contract Quote "

'Getting the file name dynamically
strName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))

'Creating the new file location and name
strFile = strName & ".pdf"
strPathFile = strPath & strFile
'export to PDF in current folder
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strPathFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& strPathFile
'MsgBox ("New file: " & strPathFile)
End Sub

 

@Budman361530 The SaveAs command is trying to save as the same type it is (default is non-macro) so you need to force it to be xlsm (adding the extension on the name is not enough). Add xlOpenXMLWorkbookMacroEnabled to your SaveAs line like this:

wb.SaveAs "C:\Users\Budman361530\Documents\New folder\Tasksheet.xlsm", xlOpenXMLWorkbookMacroEnabled

@mtarler That did the trick for saving in a Macro.  Thank you.  What am I doing wrong with trying to get the code to save dynamically, both Name and Location?

 

I have a code that I made that saves pages in a PDF, to whatever folder the WB is in at that moment, and pulls the name from the name of the workbook it self.  example.... if the WB was located in folder X, and was named  20MJNXXX. Customer 1. Job 1. REV. 1,  The PDF would be called Contract Quote. 20MJNXXX. Customer 1. Job 1. REV. 1, and save automatically in folder X. 

 

Here is the code...


Sub MacroContractQuote()

'
' Macro1 Macro
'
'
'ChDir _
'"Z:\Shared\Budman Shared\Budman Quote and Jobs\01 - Master Quote Folder Templet REV. 1"
'ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
'"Z:\Shared\Budman Shared\Budman Quote and Jobs\01 - Master Quote Folder Templet REV. 1" _
', Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
':=False, OpenAfterPublish:=False
Dim strPath As String
Dim strName As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
strPath = ThisWorkbook.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "/Contract Quote "

'Getting the file name dynamically
strName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))

'Creating the new file location and name
strFile = strName & ".pdf"
strPathFile = strPath & strFile
'export to PDF in current folder
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strPathFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& strPathFile
'MsgBox ("New file: " & strPathFile)
End Sub

 

I am trying to get the same type of results from the excel saving macro, but it's not working...

 

Sub sb_Copy_Save_Worksheet_As_Workbook()
Dim wb As Workbook
Set wb = Workbooks.Add
'Getting the file name dynamically
strName = (Left(ActiveWorkbook.Name,(InStrRev(ActiveWorkbook.Name, ".", -1,"JOB PO List") - 1))
ThisWorkbook.Sheets.Copy Before:=wb.Sheets(1)
wb.SaveAs strPath 'current folder', xlOpenXMLWorkbookMacroEnabled
End Sub