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.
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