Renaming File

ayazhoda · April 24th, 2007, 04:05 AM

Hi All,

I am trying to do following

--> back mdb file to network folder with different name which includes filename with date like XXX_Backup_230107.mdb

--> I,ve already done copying it with same name but renaming file as bit tricky

--> I am trying easiest path, copy file with same name when it copied successfully on network drive then rename it so there's no need to rename before copying it to network folder

As txtFileName refer local drive filename and i know it will rename local drive filename so is it way to rename copied file on network drive with out searching for all folder or any best programming practice

Name Me.txtFileName As "XXX_"& "_Backup_" & Format(Int(Now()), "ddmmyy")

any idea ....

Regards

Ayaz

robzyc · April 24th, 2007, 04:21 AM

Ayaz,

You need to use the WSH FileSystemObject (FSO).

Try pasting the following code into a module in your project:

Code:

Public Sub RenameFile(FilePath As String, NewFileName As String)
On Error GoTo RenameFile_Error
'Uses Windows Scripting Host to Rename the given file to the new name.
'Create a reference to "Windows Script Host Object Model" via "Tools" > "References" in VBA.

    Dim fso As New FileSystemObject

    Dim f As File
    Set f = fso.GetFile(FilePath)
    f.Name = NewFileName

RenameFile_Exit:
    Exit Sub

RenameFile_Error:
    Const FILE_NOT_FOUND = 53
    Const FILE_EXISTS = 58

    Select Case Err.Number
        Case FILE_NOT_FOUND
            MsgBox "Unable to rename the file '" & FilePath & "' as it was not found."

        Case FILE_EXISTS
            MsgBox "Unable to rename the file '" & FilePath & "', the file already exists."

        Case Else
            MsgBox "Error Renaming File '" & FilePath & "'." & vbCrLf & _
                "Error Code: " & Err.Number & vbCrLf & _
                Err.Description
    End Select
End Sub

You can then use this to rename a file.
NOTE: The error code handling is not exhaustive, these are just the most common errors.

Regards,
Rob

ayazhoda · April 24th, 2007, 05:38 AM

Thanks rob

I did this not very sure its correct logic but look tricky .....

This code for beginner programmer like me

Both Solution is easy but its good to have look on both of them its good for future task

' Stores Directory path and File Name
Dim DirectoryFileName As String

' Dialog box for Destination folder

Private Sub cmdDirectoryPath_Click()
On Error GoTo Errorhandler
    Me.txtDirectoryPath = SelectDir("", , , "Select destination for backup")

    If Not IsNull(Me.txtFileName) Then
' Separating File name with GetNamePart Method and then concatenate it with Destination folder and we rename it here to save our self from from complexity
        DirectoryFileName = Me.txtDirectoryPath & "\" & GetNamePart(Me.txtFileName)
    End If
    Exit Sub
Errorhandler:
    MsgBox "Error: " & Err.Description & " (" & Err.number & ")"

End Sub

------------------------------
' select file you want to back up
Private Sub cmdSelectFile_Click()

  On Error GoTo Errorhandler
    Me.txtFileName = OpenFile(Me.txtFileName)
    Exit Sub
Errorhandler:
    MsgBox "Error: " & Err.Description & " (" & Err.number & ")"

End Sub
-------------------------------

' extract filename and give it new name
Function GetNamePart(strIn As String) As String
Dim i As Integer
Dim strTmp As String
    For i = Len(strIn) To 1 Step -1
        If MID$(strIn, i, 1) <> "\" Then
            'strTmp = MID$(strIn, i, 1) & strTmp
            strTmp = MID$(strIn, i, 1) & strTmp

        Else
            Exit For
        End If
    Next i
    GetNamePart = Format(Int(Now()), "ddmmyy") & "_Backup_" & strTmp

End Function

call this method to actually copy files over

FileCopy Me.txtFileName, DirectoryFileName

' this any thing with files check msdn
http://msdn.microsoft.com/archive/de...ce09072000.asp

Regards

Ayaz

robzyc · April 24th, 2007, 05:43 AM

Ayaz,

Looks like that could work, I tend to forget about the "in-house" VBA file functions!
Good spot, you've taught me something! ;)

Regards,
Rob