Replacing existing files in a folder

[g diddy]

Hi there. I'm using the following code to move pdf and snp files into seperate locations after they have been used by another module. Purely for backup purposes. However if a user creates the same reports twice in one day for the same school the pdfs and snps are not placed into the folder as they already exist. As the users keep forgetting to delete the old versions the folder is starting to get a bit clogged! Please can you teach me how to replace the pdf and snp files if they already exist in the ToPath and ToPath2 locations? I've been trying for a while now and can't seem to find the right code/syntax to fit in with what I already have. I'm sorry to paste the whole code in - it's very short and basic anyways, just thought it might give you more context of what it's doing at the moment.

Sub Move_Certain_Files_To_New_Folder()
'This will move all Snapshot and PDF files from FromPath to ToPath.
'Note: It will create the folder ToPath for you with a date-time stamp
    Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String
    Dim ToPath2 As String
    Dim FileExt As String
    Dim FileExt2 As String
    Dim FNames As String
    Dim MySchool As String
    MySchool = [Forms]![Selector]![School]
    
    'move snapshots into a new folder
    FromPath = "U:\InvigReports"
    ToPath = "U:\InvigReports\" & Format(Now, "dd-mm-yyyy") _
           & " School " & MySchool & " SnapShots" & "\"

    FileExt = "*.snp*"

    If Right(FromPath, 1) <> "\" Then
        FromPath = FromPath & "\"
    End If

    FNames = Dir(FromPath & FileExt)
    If Len(FNames) = 0 Then
        MsgBox "No files in " & FromPath
        Exit Sub
    End If

    Set FSO = CreateObject("scripting.filesystemobject")

    FSO.CreateFolder (ToPath)

    FSO.MoveFile Source:=FromPath & FileExt, Destination:=ToPath
    'MsgBox "You can find the files from " & FromPath & " in " & ToPath
    
    'move pdfs into a new folder
    ToPath2 = "U:\InvigReports\" & Format(Now, "dd-mm-yyyy") _
           & " School " & MySchool & " PDFs" & "\"

    FileExt2 = "*.pdf*"
    
    FNames = Dir(FromPath & FileExt2)
    If Len(FNames) = 0 Then
        MsgBox "No files in " & FromPath
        Exit Sub
    End If

    Set FSO = CreateObject("scripting.filesystemobject")

    FSO.CreateFolder (ToPath2)

    FSO.MoveFile Source:=FromPath & FileExt2, Destination:=ToPath2
    'MsgBox "You can find the files from " & FromPath & " in " & ToPath2
End Sub

I really appreciate all your help. Thanks!

[ADezii]

Dim strFileName As String

strFileName = FromPath & FileExt

'Does the File exist in the specified Path, with
'the appropriate Extension. If it does, DELETE it!
If Dir(strFileName) <> "" Then
  Kill strFileName
End If

[Stewart Ross]

Hi. Adezii's solution works fine to delete a file before moving it if you are using actual filenames one at a time - but you aren't, as you are using the extension name (*.snp or *.pdf) throughout. The danger is that you will accidentally delete ALL files if you try to use Kill *.snp or *.pdf.

I'd suggest rewriting your routine to do away with the duplication you currently have in dealing with *.snp files followed by *.pdf, and separating out the moving of the files. You can generalise the move routine so that it goes through all files in the directory one by one, checking to see if the file already exists in the destination directory and then deleting it if it does. An example of this is shown below, which requires as arguments the file extension (a string in the form "*.xxx", where xxx is PDF or SNP or any other valid extension), the From path as a string, and the To path as a string:

Sub sMovefiles(ByVal FileExtension As String, ByVal FromPath As String, ByVal ToPath As String)
    Dim FSO As Scripting.FileSystemObject
    Dim strFileName As String
    
    Set FSO = New Scripting.FileSystemObject
    If Right(FromPath, 1) <> "\" Then
        FromPath = FromPath & "\"
    End If
    If Right(ToPath, 1) <> "\" Then
        ToPath = ToPath & "\"
    End If
    strFileName = Dir(FromPath & FileExtension)
    Do While strFileName <> ""
        If FSO.FileExists(ToPath & strFileName) Then
            FSO.DeleteFile ToPath & strFileName
        End If
        FSO.MoveFile FromPath & strFileName, ToPath & strFileName
        strFileName = Dir()
    Loop
    Set FSO = Nothing
 End Sub

-Stewart

[nico5038]

I regularly "solve" this problem by adding a date/time stamp (Formatted 'yyyymmddhhmm') after the filename, thus every copy will be "unique" and the user can remove dupes when (s)he wants.

Nic;o)

[NeoPa]

I generally follow the logic :

1. Create new file with temporary filename.
2. If (and only if) step #1 completes successfully, delete (I use Kill Filename) the file with the required filename.
3. Rename (Name TempFN As Filename) the temporary filename as the eventual one.

[g diddy]

Thank you to everyone for all your help. I've taken everyone's advice and I have got it working now. It is also a lot more efficient as i've done away with the duplication. I also decided that as the 5 pdf files that are created have a specific temporary name each, that I would delete them from the folder with the kill statement (if they already existed) only if the new pdf(s) being moved had the same temporary name(s). Works a treat.

Thanks again, never would have got this far otherwise!

[NeoPa]

I'm very pleased to hear it G_Diddy. It sounds like you have a full and complete solution now. Thanks for posting :)