Renaming an Active Workbook

[sassriverrat]

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

 

[JLGWhiz]

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.

 

[sassriverrat]

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?

 

[spencer_time]

You could do something similar to this:

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

 

[spencer_time]

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

Try this one instead:

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

 

[Jaafar Tribak]

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" ?

 

[sassriverrat]

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!

 

[JLGWhiz]

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.

 

[spencer_time]

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

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.