By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,341 Members | 1,366 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,341 IT Pros & Developers. It's quick & easy.

Replacing existing files in a folder

P: 54
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.

Expand|Select|Wrap|Line Numbers
  1. Sub Move_Certain_Files_To_New_Folder()
  2. 'This will move all Snapshot and PDF files from FromPath to ToPath.
  3. 'Note: It will create the folder ToPath for you with a date-time stamp
  4.     Dim FSO As Object
  5.     Dim FromPath As String
  6.     Dim ToPath As String
  7.     Dim ToPath2 As String
  8.     Dim FileExt As String
  9.     Dim FileExt2 As String
  10.     Dim FNames As String
  11.     Dim MySchool As String
  12.     MySchool = [Forms]![Selector]![School]
  14.     'move snapshots into a new folder
  15.     FromPath = "U:\InvigReports"
  16.     ToPath = "U:\InvigReports\" & Format(Now, "dd-mm-yyyy") _
  17.            & " School " & MySchool & " SnapShots" & "\"
  19.     FileExt = "*.snp*"
  21.     If Right(FromPath, 1) <> "\" Then
  22.         FromPath = FromPath & "\"
  23.     End If
  25.     FNames = Dir(FromPath & FileExt)
  26.     If Len(FNames) = 0 Then
  27.         MsgBox "No files in " & FromPath
  28.         Exit Sub
  29.     End If
  31.     Set FSO = CreateObject("scripting.filesystemobject")
  33.     FSO.CreateFolder (ToPath)
  35.     FSO.MoveFile Source:=FromPath & FileExt, Destination:=ToPath
  36.     'MsgBox "You can find the files from " & FromPath & " in " & ToPath
  38.     'move pdfs into a new folder
  39.     ToPath2 = "U:\InvigReports\" & Format(Now, "dd-mm-yyyy") _
  40.            & " School " & MySchool & " PDFs" & "\"
  42.     FileExt2 = "*.pdf*"
  44.     FNames = Dir(FromPath & FileExt2)
  45.     If Len(FNames) = 0 Then
  46.         MsgBox "No files in " & FromPath
  47.         Exit Sub
  48.     End If
  50.     Set FSO = CreateObject("scripting.filesystemobject")
  52.     FSO.CreateFolder (ToPath2)
  54.     FSO.MoveFile Source:=FromPath & FileExt2, Destination:=ToPath2
  55.     'MsgBox "You can find the files from " & FromPath & " in " & ToPath2
  56. End Sub
I really appreciate all your help. Thanks!
Jan 15 '10 #1
Share this Question
Share on Google+
6 Replies

Expert 5K+
P: 8,669
Expand|Select|Wrap|Line Numbers
  1. Dim strFileName As String
  3. strFileName = FromPath & FileExt
  5. 'Does the File exist in the specified Path, with
  6. 'the appropriate Extension. If it does, DELETE it!
  7. If Dir(strFileName) <> "" Then
  8.   Kill strFileName
  9. End If
Jan 15 '10 #2

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. Sub sMovefiles(ByVal FileExtension As String, ByVal FromPath As String, ByVal ToPath As String)
  2.     Dim FSO As Scripting.FileSystemObject
  3.     Dim strFileName As String
  5.     Set FSO = New Scripting.FileSystemObject
  6.     If Right(FromPath, 1) <> "\" Then
  7.         FromPath = FromPath & "\"
  8.     End If
  9.     If Right(ToPath, 1) <> "\" Then
  10.         ToPath = ToPath & "\"
  11.     End If
  12.     strFileName = Dir(FromPath & FileExtension)
  13.     Do While strFileName <> ""
  14.         If FSO.FileExists(ToPath & strFileName) Then
  15.             FSO.DeleteFile ToPath & strFileName
  16.         End If
  17.         FSO.MoveFile FromPath & strFileName, ToPath & strFileName
  18.         strFileName = Dir()
  19.     Loop
  20.     Set FSO = Nothing
  21.  End Sub
Jan 15 '10 #3

Expert 2.5K+
P: 3,072
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.

Jan 15 '10 #4

Expert Mod 15k+
P: 31,661
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.
Jan 17 '10 #5

P: 54
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!
Jan 20 '10 #6

Expert Mod 15k+
P: 31,661
I'm very pleased to hear it G_Diddy. It sounds like you have a full and complete solution now. Thanks for posting :)
Jan 21 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.