How to rename a workbook other than using Name and FileSystemObject MoveFile

0 votes

Let's say I need to rename a workbook on my desktop with the name Test.xlsm to Test2.xlsm in the same location.
I may rename it as follows by using the Name statement:

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.

Feb 11, 2023 in Others by narikkadan
• 63,600 points
461 views

1 answer to this question.

0 votes

Use  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
answered Feb 11, 2023 by Kithuzzz
• 38,000 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

How to find the last row in a column using openpyxl normal workbook?

ws.max_row will give you the number of rows ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 63,600 points
6,618 views
0 votes
1 answer

What is a name function in JavaScript & how to define it?

A named function declares a name as ...READ MORE

answered Mar 7, 2019 in Others by Frankie
• 9,830 points
4,694 views
0 votes
1 answer

Generate VCards from Excel using VBA

Solution  Create a class called CContact with getters ...READ MORE

answered Nov 21, 2022 in Others by narikkadan
• 63,600 points
770 views
0 votes
1 answer

VBA Loop To Import Changing File Names

You can use a FOR loop and ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,600 points
890 views
0 votes
1 answer

Is there a hierarchy inferring algorithm available in python?

Convert the list of values to the ...READ MORE

answered Jan 15, 2023 in Others by narikkadan
• 63,600 points
431 views
0 votes
1 answer

How to make a auto counter to print pages

This will loop through all of those ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,600 points
454 views
0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,000 points
1,789 views
0 votes
1 answer

How to query a matrix for multiple values and receive value

Try: =IFERROR(INDEX(SORT(SORT(FILTER(MATRIX, (LENGTHS>length)*(WEIGHTS>weight)),1,1),2,1),1,3),1) , where MATRIX, LENGTHS, and WEIGHTS ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,000 points
644 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP