Excel Macro Man: How to rename file in a directory

Excel Macro

Sub rename_file()

Dim OldName, NewName

clearfilepath

SearchFile

Sheets("FilePath").Select

Range("A65536").Select

ro1 = Selection.End(xlUp).Row

For i = 2 To ro1

   

    OldName = Cells(i, 1).Value '"D:\Documents and Settings\xxxx\Desktop\2010-3-30\chr0101.591"

    NewName = OldName & ".csv" ' Define file names.

    Name OldName As NewName ' Rename file.

Next i

MsgBox "Its done !!"

End Sub

Function SearchFile()

Set fs = Application.FileSearch

With fs

'‘this code automatically searches for files in desktop – Source File folder

    .LookIn = Environ("userprofile") & Application.PathSeparator & "Desktop" & Application.PathSeparator & "Source File" & Application.PathSeparator

    .FileType = msoFileTypeAllFiles

    If .Execute > 0 Then

        Sheets("FilePath").Select

        For i = 1 To .FoundFiles.Count

             Range("A" & (i + 1)).Value = .FoundFiles(i)

        Next i

    Else

        'MsgBox "There were no Binder files found."

    End If

End With

End Function

Function clearfilepath()

Sheets("FilePath").Select

Range("A2:A65536").Select

Selection.ClearContents

End Function

Subtract the given no.of days from today

Enter the no.of days to be subtracted from today

for eg:- if Now returns 3rd April 2010

and you enter 3 ... then it would return 31st March 2010

Sub Sub_dates()

x = InputBox("Enter the no.of days to be subtracted frm today")
y = DateAdd("d", -x, Now)
z = Mid(y, 1, InStr(1, y, " ") - 1)

MsgBox z
End Sub

How to rename file in a directory

Sub rename_file()

Dim OldName, NewName

clearfilepath

SearchFile

Sheets("FilePath").Select

Range("A65536").Select

ro1 = Selection.End(xlUp).Row

For i = 2 To ro1

   

    OldName = Cells(i, 1).Value '"D:\Documents and Settings\xxxxxxxxx\Desktop\2010-3-30\chr0101.591"

    NewName = OldName & ".csv" ' Define file names.

    Name OldName As NewName ' Rename file.

Next i

MsgBox "Its done !!"

End Sub

Function SearchFile()

Set fs = Application.FileSearch

With fs

'‘this code automatically searches for files in desktop – Source File folder

    .LookIn = Environ("userprofile") & Application.PathSeparator & "Desktop" & Application.PathSeparator & "Source File" & Application.PathSeparator

    .FileType = msoFileTypeAllFiles

    If .Execute > 0 Then

        Sheets("FilePath").Select

        For i = 1 To .FoundFiles.Count

             Range("A" & (i + 1)).Value = .FoundFiles(i)

        Next i

    Else

        'MsgBox "There were no Binder files found."

    End If

End With

End Function

Function clearfilepath()

Sheets("FilePath").Select

Range("A2:A65536").Select

Selection.ClearContents

End Function