excelvbaautomationfile-renamewindows-scripting

How to rename a workbook other than using (Name) and (FileSystemObject. MoveFile)?


Suppose I have a workbook on my desktop with name Test.xlsm and I need to rename it to Test2.xlsm in the same path.
I can rename it using Name statement as the following :

Sub Rename_using_Name()
 
    Dim oldName As String, newName As String
 
    oldName = "D:\Users\UserName\Desktop\Test.xlsm"
    newName = "D:\Users\UserName\Desktop\Test2.xlsm"
 
    Name oldName As newName
 
End Sub

Or, I can rename using FileSystemObject , like the following:

Sub Rename_using_FileSystemObject()
 
   Dim fso As Object, oldName As String, newName As String
 
   Set fso = CreateObject("Scripting.FileSystemObject")
 
   oldName = "D:\Users\UserName\Desktop\Test.xlsm"
   newName = "D:\Users\UserName\Desktop\Test2.xlsm"
 
   fso.MoveFile oldName, newName
 
End Sub

I seek for any additional means to rename a workbook other than the above cited ones.
In advance, great thanks for all your help.


Solution

  • You already have easy methods to achieve what you want... Here is one more way which uses SHFileOperation API

    Option Explicit
    
    Private Declare PtrSafe Function SHFileOperation Lib "shell32.dll" _
    Alias "SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As LongPtr
    
    Private Const FOF_SIMPLEPROGRESS = &H100
    Private Const FO_RENAME = &H4
    
    Private Type SHFILEOPSTRUCT
        hWnd As LongPtr
        wFunc As Long
        pFrom As String
        pTo As String
        fFlags As Integer
        fAborted As Boolean
        hNameMaps As LongPtr
        sProgress As String
    End Type
    
    Sub Sample()
        SHRenameFile "D:\Users\UserName\Desktop\Test.xlsm", _
                     "D:\Users\UserName\Desktop\Test2.xlsm"
    End Sub
    
    Public Sub SHRenameFile(ByVal strSource As String, ByVal strTarget As String)
        Dim op As SHFILEOPSTRUCT
    
        With op
            .wFunc = FO_RENAME
            .pTo = strTarget
            .pFrom = strSource
            .fFlags = FOF_SIMPLEPROGRESS
        End With
    
        '~~> Perform operation
        SHFileOperation op
    End Sub