Thursday, June 2, 2011

Macro to rename file

If you want to rename file Using VBA . Try this macro

Function file_exists(fl_path As String) As String
    If Dir(fl_path) <> "" And fl_path <> "" Then
        file_exists = "Exists"
        file_exists = "Not Exists"
    End If
End Function

Sub rename_file()

    Dim filenm As String
    Dim newname As String
    Dim newpath As String
    ' old file name
    filenm = "C:\Documents and Settings\user\Desktop\sample.xlsm"
    'new File Name
    newname = "Sample_1" ' do not add file extension
    ' new path
    newpath = VBA.Left(filenm, InStrRev(filenm, "\")) & newname & VBA.Right(filenm, Len(filenm) - InStrRev(filenm, ".") + 1)
    ' Check if file exists which we want to rename
    If file_exists(filenm) <> "Exists" Then
        MsgBox "File does not exists can not be renamed ", vbInformation, "Note:"
        Exit Sub
    End If
    ' check if any file already exists with same name
    If file_exists(newpath) = "Exists" Then
        MsgBox "File already exits with this name . Please choose any other name ", vbInformation, "Note:"
        Exit Sub
    End If
    ' rename the file finally
    Name filenm As newpath

End Sub

Download Working File

No comments:

Post a Comment