If I want to rename an opened workbook, I need to close it firstly, and then rename it which it is a little tedious.
on 3rd party software “Office Tab” , It has this feature to Rename opened workbook without firstly close it.
Is it possible to be done by Excel without using other software ,even by using VBA soultion.
In advance , greatful for any help.

CodePudding user response：

Rename an Open Workbook

Option Explicit

Sub RenameMe()
    
    Const ibPrompt As String = "Enter a new file name"
    Const ibTitle As String = "Rename Me"
    
    Dim wb As Workbook: Set wb = ActiveWorkbook
    Dim DotPosition As Long: DotPosition = InStr(1, wb.Name, ".")
    If DotPosition = 0 Then Exit Sub
    
    Dim ibDefault As String: ibDefault = Left(wb.Name, DotPosition - 1)
    
    Dim NewBaseName As String
    NewBaseName = InputBox(ibPrompt, ibTitle, ibDefault)
    If Len(NewBaseName) = 0 Then Exit Sub
    
    Dim FilePath As String: FilePath = wb.FullName
    Dim FolderPath As String: FolderPath = wb.Path & Application.PathSeparator
    Dim Extension As String: Extension = Right(Extension, DotPosition)
    
    Dim ErrNum As Long
    On Error Resume Next
        wb.SaveAs FolderPath & NewBaseName & Extension
        ErrNum = Err.Number
    On Error GoTo 0
    
    If ErrNum = 0 Then
        Kill FilePath
    Else
        MsgBox "Could not rename.", vbCritical, "Rename Me"
    End If
    
End Sub

CodePudding user response：

there are various solutions to this problem,

the first one will be to run the following:

ActiveWorkbook.SaveAs "MyFile.xls"
Kill "MyPreviousFileName.xls"

you do have to save it in order to change its name. and if you want to save the old copy, you can remove the kill function

if you want it to be more precise you can do as follows (edit: added @funthomas's suggestion):

Sub Macro2()

Dim rootDir as string
Dim FileName as String

rootDir = "\Desktop"
rootDir  = Replace(rootDir, "@1", environ(UserProfile))

FileName = "Test"

ActiveWorkbook.SaveAs rootDir & FileName & ".xlsm"

End Sub