重新命名打开的工作簿，而不首先关闭它？

Question

如果我想重命名打开的工作簿，我需要先关闭它，然后重命名它，这有点乏味。

在第三方软件“Office”上，它具有Rename opened workbook without firstly close it的这一特性。

如果不使用其他软件，即使使用VBA解决，也可以用Excel完成吗？

提前，很乐意提供任何帮助。

Answer 1

重命名打开的工作簿

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

Answer 2

这个问题有各种各样的解决办法，

第一个步骤是运行以下命令：

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

你必须保存它才能改变它的名字。如果要保存旧副本，可以删除kill函数。

如果您希望它更精确，您可以这样做(编辑:添加了@funthomas的建议)：

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