Renaming an Active Workbook

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
655
I've seen a lot of forums about this but wanted to inquire if there was a favorite way to rename an active workbook. The previous name (name being replaced) has to be dynamic, it could be any number of things while the new name is fixed. This is going into the middle of a macro that already resets the workbook back to "template" mode in case someone writes over the template accidentally.

Thanks
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
The only way I know to rename an ActiveWorkbook is to do a SaveAs using a different file name and then kill the old file.. You can rename closed files with code without using the SaveAs.
 
Last edited:
Upvote 0
The only way I know to rename an ActiveWorkbook is to do a SaveAs using a different file name and then kill the old file.. You can rename closed files with code without using the SaveAs.


Which makes sense. The part that I can't comprehend, though, is how can I tell excel to kill a file that is not consistent? Meaning, if someone renames the file and I want to use the "reset button", that file could be named anything...it's not like every person that gets the wise idea to rename something uses the same new name. Does that make sense?
 
Upvote 0
You could do something similar to this:

Code:
Sub renameActiveWB()
Dim oldFileName As String
Dim newFileName As String
Dim oldPath As String
oldPath = ThisWorkbook.Path
newFileName = "thisIsTheNewFile" 'define name for new filename

    oldFileName = Application.ActiveWorkbook.FullName 'save old filename and path to variable
    ActiveWorkbook.SaveAs oldPath & newFileName 'save file as name defined
    Kill oldFileName 'delete file defined by variable oldFileName

End Sub
 
Upvote 0
Sorry disregard my last post, I left out a backslash and it saved your new file in the wrong location.

Try this one instead:

Code:
Sub renameActiveWB()
Dim oldFileName As String
Dim newFileName As String
Dim oldPath As String
oldPath = ThisWorkbook.Path
newFileName = "thisIsTheNewFile2.xlsm" 'define name for new filename

    oldFileName = Application.ActiveWorkbook.FullName 'save old filename and path to variable
    ActiveWorkbook.SaveAs oldPath & "\" & newFileName 'save file as name defined
    Kill oldFileName 'delete file defined by variable oldFileName

End Sub
 
Upvote 0
I've seen a lot of forums about this but wanted to inquire if there was a favorite way to rename an active workbook. The previous name (name being replaced) has to be dynamic, it could be any number of things while the new name is fixed. This is going into the middle of a macro that already resets the workbook back to "template" mode in case someone writes over the template accidentally.

Thanks

By Active Workbook , are you referring to the current workbook that is actually running the macro. In other words "ThisWorkbook" ?
 
Upvote 0
Interesting- I'll run it- it's simply enough....

Somehow I thought when you "Save As" it would switch the activeworkbook and therefore this could have have attempted to run to kill the newly saved workbook. Makes sense.

Thanks!
 
Upvote 0
When you SaveAs the new workbook name is the ActiveWorkbook and if you do not change the file type from .xlsm it will retain the code. Regardless, the code continues to run in that instance of Excel until you shut Excel down. Then at that point you would run into the name change problem. I have no idea how to control some user saving the file as a different name and making that visible to all potential users if they do not follow naming conventions for the organization they work for. Except maybe eliminate the user who does not comply.
 
Last edited:
Upvote 0
I just did a little research and threw this together as a proof of concept, perhaps it's more of what you're looking for.

Disables regular Save and instead forces to SaveAS
Also gives user a small popup that explains them to follow company policy

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim combiName As String, curYear As String, curMonth As String, curDay As String, curHour As String, curMinute As String, curName As String, ext As String, sNewName As String, fPth As Object
Set fPth = Application.FileDialog(msoFileDialogSaveAs)
curYear = Year(Date)
curMonth = Month(Date)
    If curMonth < 10 Then
        curMonth = "0" & curMonth
    End If
curDay = Day(Date)
     If curDay < 10 Then
        curDay = "0" & curDay
    End If
curHour = Hour(Time())
     If curHour < 10 Then
        curHour = "0" & curHour
    End If
curMinute = Minute(Time())
     If curMinute < 10 Then
        curMinute = "0" & curMinute
    End If
sep = "."
curName = "current_filename"
ext = ".xlsm"
Cancel = True
combiName = curName & sep & curMonth & sep & curDay & sep & curYear & ext
msg = "Please name file in accordance with company policy."
MsgBox msg
    With fPth
        .InitialFileName = combiName
        .Title = "Save your File:"
        .InitialView = msoFileDialogViewList
        .Show
    End With
End Sub


EDIT 13:01CST: This code should be placed within the "ThisWorkbook" module and macros must be enabled in order for it to stop user from pressing Save.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,718
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top