Results 1 to 9 of 9

Thread: Renaming an Active Workbook
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2018
    Posts
    564
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Renaming an Active Workbook

    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

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,061
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Renaming an Active Workbook

    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 by JLGWhiz; Oct 12th, 2019 at 06:09 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    Board Regular
    Join Date
    Oct 2018
    Posts
    564
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Renaming an Active Workbook

    Quote Originally Posted by JLGWhiz View Post
    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?

  4. #4
    New Member spencer_time's Avatar
    Join Date
    Sep 2019
    Location
    NE Alabama, USA
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Renaming an Active Workbook

    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

  5. #5
    New Member spencer_time's Avatar
    Join Date
    Sep 2019
    Location
    NE Alabama, USA
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Renaming an Active Workbook

    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

  6. #6
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,393
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Renaming an Active Workbook

    Quote Originally Posted by sassriverrat View Post
    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" ?
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  7. #7
    Board Regular
    Join Date
    Oct 2018
    Posts
    564
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Renaming an Active Workbook

    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!

  8. #8
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,061
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Renaming an Active Workbook

    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 by JLGWhiz; Oct 13th, 2019 at 09:52 AM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  9. #9
    New Member spencer_time's Avatar
    Join Date
    Sep 2019
    Location
    NE Alabama, USA
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Renaming an Active Workbook

    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 by spencer_time; Oct 13th, 2019 at 03:02 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •