Creating a file copy and move process from one location to another location? (1 Viewer)

Sampoline

Member
Local time
Today, 12:00
Joined
Oct 19, 2020
Messages
161
Hi. I am trying to create an access database that will perform file backups for me. So I'll need to use code to do this. Okay, let's breakdown what I need to do into steps:

1) Set the UNC path of where all files are located (they will all be found in one main folder that contains sub-folders, so maybe we can set a particular location; assume this as Location A)
2) COPY all the files from there (which will all be TIF's) and PASTE them into a new specified backup location (assume as Location B). But the code must check if the file(s) exist (using the file name I assume) in this new location first. If they do already exist, it must not overwrite the ones pre-existing and skip past it to the next file. But if it's not existing, then it should copy them over.
3) After it's done doing that, it must MOVE the file in the old location (Location A) to a different location that I require (Location C). But once again, that should only happen if (in Step 2) the files got copied over into Location B. Or maybe if that's too complicated, the files just do that same thing as Step 2 and run a check if file exists and move them over to the new location (Location C) if not.

I also want this entire process logged into a table. So like I want the file name, date/time it was copied/moved and the user that performed the task populated in that table.

Now maybe this can all be coded to a single button that performs the tasks mentioned above. Or I could use an Application.FileDialog(PickType) type way of choosing the folder and it grabs all the files in the subfolders and does the same thing. Whatever seems more reasonable.

I have a template database I've just been playing around with from a previous database that I had to create for a directory list function. I'll attach it to this post. In it you'll find a file dialog picker process I just mentioned. If that's of any use than do have a look and play around with it as you please. But if that's not required and we can do everything in a single button click, I'm definitely welcome to that instead.

Thanks!
 

Attachments

  • FileWorkflowConcept.accdb
    608 KB · Views: 19

bastanu

Active member
Local time
Yesterday, 19:00
Joined
Apr 13, 2010
Messages
706
Hi,
This should be very easy to achive, use either Dir() or FIle System Object to loop through your A folder and for each file, store its name in a string variable (sFileName), use DIr("B\" & sFilename )>0 to check for the existance of the file in B and skip to the next file if it does. If not first copy the file from A to B (FileCopy "A\" & sFileName , "B\" & sFileName) then move from A to C (Name"A\" & sFileName As "C\" & sFileName) and finaly insert a record in the log table using CurrentDB.Execute with an "Insert Into" SQL statement.

Here are some links that should put you well on your way:

Cheers,
 

Sampoline

Member
Local time
Today, 12:00
Joined
Oct 19, 2020
Messages
161
Hi,
This should be very easy to achive, use either Dir() or FIle System Object to loop through your A folder and for each file, store its name in a string variable (sFileName), use DIr("B\" & sFilename )>0 to check for the existance of the file in B and skip to the next file if it does. If not first copy the file from A to B (FileCopy "A\" & sFileName , "B\" & sFileName) then move from A to C (Name"A\" & sFileName As "C\" & sFileName) and finaly insert a record in the log table using CurrentDB.Execute with an "Insert Into" SQL statement.

Here are some links that should put you well on your way:

Cheers,
Hi Vlad, thanks for that.

I've so far successfully worked out how to copy the files from Location A to Location B (only if they don't exist in Location B already).

But I'm having trouble understanding how to do the next few parts. I've attached the file for review, if you could help me out.

Thanks.
 

Attachments

  • FileWorkflowConcept.zip
    71.6 KB · Views: 17

bastanu

Active member
Local time
Yesterday, 19:00
Joined
Apr 13, 2010
Messages
706
Hi Sam,
Here it is, please read the comments and modify as required.
Cheers,
Vlad
 

Attachments

  • FileWorkflowConceptVlad.zip
    35.1 KB · Views: 18

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:00
Joined
Feb 28, 2001
Messages
19,415
There are many ways to do this. Vlad has offered a solution. I'll let you look over his options first. However, I will add the comment that using the Office FileSystemObject (technically, a Scripting library) will probably make this very easy because of the many file primitive functions available through the FSO. His suggestion does, in fact, use the FSO.

If you want to read up on FSO, here is a link to the MS documents for the "top" of the documentation tree for it:


From there, you can drill down to look up anything you didn't understand in Vlad's offering.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:00
Joined
May 21, 2018
Messages
4,779
This may help. This db allows you to pick a top level folder. It spans the directory and logs all files and folders and gets the count. It puts it into a treeview (not relevant here).
But you can see the code for spanning and logging.
 

Attachments

  • LogFileDirectory V1 Alerts.accdb
    1.1 MB · Views: 22

bastanu

Active member
Local time
Yesterday, 19:00
Joined
Apr 13, 2010
Messages
706
Hi Sam,
Please use this updated file (in the first one I had .jpg instead of .tif in one VBA line as I wanted to test it and didn't have any tif files handy).
Cheers,
Vlad
 

Attachments

  • FileWorkflowConceptVlad.zip
    26.9 KB · Views: 18

Sampoline

Member
Local time
Today, 12:00
Joined
Oct 19, 2020
Messages
161
Hi Sam,
Here it is, please read the comments and modify as required.
Cheers,
Vlad
There are many ways to do this. Vlad has offered a solution. I'll let you look over his options first. However, I will add the comment that using the Office FileSystemObject (technically, a Scripting library) will probably make this very easy because of the many file primitive functions available through the FSO. His suggestion does, in fact, use the FSO.

If you want to read up on FSO, here is a link to the MS documents for the "top" of the documentation tree for it:


From there, you can drill down to look up anything you didn't understand in Vlad's offering.
This may help. This db allows you to pick a top level folder. It spans the directory and logs all files and folders and gets the count. It puts it into a treeview (not relevant here).
But you can see the code for spanning and logging.
This thread has the code for selecting files or a folder and moving renaming. You may have that part already.
Hi Sam,
Please use this updated file (in the first one I had .jpg instead of .tif in one VBA line as I wanted to test it and didn't have any tif files handy).
Cheers,
Vlad
Thanks a lot Vlad @bastanu , apart from it working, the code itself makes a lot of sense to me; I knew I was kinda on the right track but just didn't know how to put it all together. You've spared me some headache hours this morning. Cheers for that sir and thanks for taking time out of your day to do it.

Hi @The_Doc_Man I had read over the documentation on FSO, I'm starting to understand more about it. It's also a useful link to keep handy when referencing methods and what they do in an FSO.

Wow @MajP this is super extensive. I've only just grazed the surface with your directory database. It's certainly a lot to get my head around. Thankyou for sending me this. Because I have been working on improving my directory log database and will definetly take time to continue reading your code and comments further. Especially this treeview is very interesting and intricate. I will definitely contact you again soon with questions I bet! Cheers.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:00
Joined
May 21, 2018
Messages
4,779
@Sampoline
That demo was based on about three long threads on this forum designed to demo some concepts for the OPs. The main question asked recently was how to recursively span all the folders and subfolders to get file counts and log them into a table. This is done with a recursive call (the function calls itself), and if you have never done this or seen it can be real confusing. The code to do that is here

Code:
Private Sub SpanFolders(SourceFolderFullName As String, Optional ParentID As Long = 0, Optional ByVal FolderLevel = 0)

' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
    'Dim FSO As Object 'Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder 'Scripting.Folder
    Dim SubFolder As Scripting.Folder 'Scripting.Folder
    Dim FileItem As Scripting.file 'Scripting.File
    Dim Contador As Integer
   ' Dim ParentID As Long
    Contador = 0
    
    Set SourceFolder = FSO.GetFolder(SourceFolderFullName)
    
    FolderLevel = FolderLevel + 1
    LogFilesFolders SourceFolder.Name, SourceFolder.Path, SourceFolder.Type, ParentID, fft_Folder, FolderLevel
    ParentID = GetFolderID(SourceFolder.Path)
    For Each FileItem In SourceFolder.Files
        If (FileItem.Attributes And 2) <> 2 And (FileItem.Attributes And 4) <> 4 And FileItem.Type <> "Archivo SRT" Then
            LogFilesFolders FileItem.Name, FileItem.Path, FileItem.Type, ParentID, fft_File, FolderLevel
        End If
        If Not ((FileItem.Attributes And 2) <> 2 And (FileItem.Attributes And 4) <> 4 And FileItem.Type <> "Archivo SRT" _
        And FileItem.Type <> "MP4 Video File (VLC)") Then
            Alert FileItem.Name, SourceFolder.Name
        End If
    Next FileItem
    
    For Each SubFolder In SourceFolder.SubFolders
        ParentID = GetFolderID(SourceFolder.Path) ' The record has just been added so get PK by name
    '   LogFilesFolders SubFolder.Name, SubFolder.Path, SubFolder.Type, ParentID, fft_Folder, FolderLevel
       If (SubFolder.Attributes And 2) <> 2 And (SubFolder.Attributes And 4) <> 4 Then
            SpanFolders SubFolder.Path, ParentID, FolderLevel
       End If
    Next SubFolder

    Set FileItem = Nothing
    Set SourceFolder = Nothing
    'MsgBox Contador
End Sub

There are some bells and whistles here to check for certain file types, but this is the basis for any directory span. You will see that the code calls itself
Code:
SpanFolders SubFolder.Path, ParentID, FolderLevel

The treeview stuff was just a bonus I provided the OP since I had it. I demo here how to turn any query into a treeview.

The treeview can be displayed in an Active X treeview or in this case it uses a very detailed class module and some MS Forms components. The link explains that.
 

Sampoline

Member
Local time
Today, 12:00
Joined
Oct 19, 2020
Messages
161
@Sampoline
That demo was based on about three long threads on this forum designed to demo some concepts for the OPs. The main question asked recently was how to recursively span all the folders and subfolders to get file counts and log them into a table. This is done with a recursive call (the function calls itself), and if you have never done this or seen it can be real confusing. The code to do that is here

Code:
Private Sub SpanFolders(SourceFolderFullName As String, Optional ParentID As Long = 0, Optional ByVal FolderLevel = 0)

' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
    'Dim FSO As Object 'Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder 'Scripting.Folder
    Dim SubFolder As Scripting.Folder 'Scripting.Folder
    Dim FileItem As Scripting.file 'Scripting.File
    Dim Contador As Integer
   ' Dim ParentID As Long
    Contador = 0
   
    Set SourceFolder = FSO.GetFolder(SourceFolderFullName)
   
    FolderLevel = FolderLevel + 1
    LogFilesFolders SourceFolder.Name, SourceFolder.Path, SourceFolder.Type, ParentID, fft_Folder, FolderLevel
    ParentID = GetFolderID(SourceFolder.Path)
    For Each FileItem In SourceFolder.Files
        If (FileItem.Attributes And 2) <> 2 And (FileItem.Attributes And 4) <> 4 And FileItem.Type <> "Archivo SRT" Then
            LogFilesFolders FileItem.Name, FileItem.Path, FileItem.Type, ParentID, fft_File, FolderLevel
        End If
        If Not ((FileItem.Attributes And 2) <> 2 And (FileItem.Attributes And 4) <> 4 And FileItem.Type <> "Archivo SRT" _
        And FileItem.Type <> "MP4 Video File (VLC)") Then
            Alert FileItem.Name, SourceFolder.Name
        End If
    Next FileItem
   
    For Each SubFolder In SourceFolder.SubFolders
        ParentID = GetFolderID(SourceFolder.Path) ' The record has just been added so get PK by name
    '   LogFilesFolders SubFolder.Name, SubFolder.Path, SubFolder.Type, ParentID, fft_Folder, FolderLevel
       If (SubFolder.Attributes And 2) <> 2 And (SubFolder.Attributes And 4) <> 4 Then
            SpanFolders SubFolder.Path, ParentID, FolderLevel
       End If
    Next SubFolder

    Set FileItem = Nothing
    Set SourceFolder = Nothing
    'MsgBox Contador
End Sub

There are some bells and whistles here to check for certain file types, but this is the basis for any directory span. You will see that the code calls itself
Code:
SpanFolders SubFolder.Path, ParentID, FolderLevel

The treeview stuff was just a bonus I provided the OP since I had it. I demo here how to turn any query into a treeview.

The treeview can be displayed in an Active X treeview or in this case it uses a very detailed class module and some MS Forms components. The link explains that.
Awesome, thanks for that Maj!
 

Sampoline

Member
Local time
Today, 12:00
Joined
Oct 19, 2020
Messages
161
Hi Sam,
Please use this updated file (in the first one I had .jpg instead of .tif in one VBA line as I wanted to test it and didn't have any tif files handy).
Cheers,
Vlad
Hi Vlad, say if in Location_Source a more updated version of the .tif is created intentionally, and I need that to replace the .tif in Location_Copy, how would I go about that.

So let me give a scenario:
A file is created into Location_Source, it copies into Location_Copy but only if it doesn't already exist. Then it moves the file in Location_Source into Location_Move. Now say there is an issue with the file, I may manually remove the image from Location_Move so that it can be re-processed as a new one. So it is recreated into Location_Source, but now there is already a copy of the file in Location_Copy, how would I communicate with Location_Copy to accept the new file and delete the previous copy (or at least move that older file to a subfolder, or even move the new one to a subfolder instead and it can be manually reviewed). I'm open to options!
 

bastanu

Active member
Local time
Yesterday, 19:00
Joined
Apr 13, 2010
Messages
706
Many ways to do it... depends on what you want. In your scenario above looks like you want to overwrite the Location_Copy. You can do that by default (eliminate the check in the code for the presence of the file in l_copy) by setting the overwrite argument of the CopyFile method to True.

Or you can leave the check in place and if the file exists in Location_Copy move it into a subfolder (Location_Copy\OldVersions) using Name (Name Location_Copy & sFileName As Location_Copy\OldVersions & sFileName) - you can append the date and time to the file name as well. Once the file is moved do your CopyFile from Source to Copy.

Cheers,
Vlad
 

Sampoline

Member
Local time
Today, 12:00
Joined
Oct 19, 2020
Messages
161
Many ways to do it... depends on what you want. In your scenario above looks like you want to overwrite the Location_Copy. You can do that by default (eliminate the check in the code for the presence of the file in l_copy) by setting the overwrite argument of the CopyFile method to True.

Or you can leave the check in place and if the file exists in Location_Copy move it into a subfolder (Location_Copy\OldVersions) using Name (Name Location_Copy & sFileName As Location_Copy\OldVersions & sFileName) - you can append the date and time to the file name as well. Once the file is moved do your CopyFile from Source to Copy.

Cheers,
Vlad
So this what I added to this is: Name ToPath & sFileName As ToPath & "\" & "OldVersions" & sFileName

Code:
    'Check if file already exists in ToPath and skip if yes
    If Dir(ToPath & "\" & sFileName) <> "" Then GoTo Next_File
   
    Name ToPath & sFileName As ToPath & "\" & "OldVersions" & sFileName
   
    'Then copy the file into ToPath
    FSO.CopyFile FromPath & "\" & sFileName, ToPath & "\" & sFileName
   
    'Now insert record in tblLog -note that the relevant record table was set in the beginning
    sSQL = "INSERT INTO tblLog ( FName, FPath, FSize, FDateCreated, FDateLastModified, FDateLastAccessed, FType, FOwner )" & _
        " VALUES ( '" & sFileName & "', '" & FSOFile.Path & "' , " & FSOFile.Size & ", #" & _
        FSOFile.DateCreated & "# , #" & FSOFile.DateLastModified & "#, #" & FSOFile.DateLastAccessed & "#, '" & FSOFile.Type & _
        "', '" & Environ("UserName") & "');"

    CurrentDb.Execute sSQL, dbFailOnError

Is this correct?
 

bastanu

Active member
Local time
Yesterday, 19:00
Joined
Apr 13, 2010
Messages
706
Not quite Sam as it would never get there cause the previous like ships it to next file.
Please see attached.

Cheers,
Vlad
 

Attachments

  • FileWorkflowConceptVlad2.zip
    30.5 KB · Views: 14

Sampoline

Member
Local time
Today, 12:00
Joined
Oct 19, 2020
Messages
161
Not quite Sam as it would never get there cause the previous like ships it to next file.
Please see attached.

Cheers,
Vlad
HI Vlad, the process doesn't seem to be working?

I ran the sub and it didn't copy, move nor create the folder.
 

bastanu

Active member
Local time
Yesterday, 19:00
Joined
Apr 13, 2010
Messages
706
Can you put a break and step through the code and see what's happening. It is getting late hear and gotta' do dinner, I can look at it in the morning.

Cheers,
 

Sampoline

Member
Local time
Today, 12:00
Joined
Oct 19, 2020
Messages
161
Sorry for holding you up Vlad, take your time. Will talk with you whenever you are next free tomorrow. I will try the break and step as you suggested till now.

Thanks for your time!
 

bastanu

Active member
Local time
Yesterday, 19:00
Joined
Apr 13, 2010
Messages
706
Hi,
Here is the revised copy, just tested it and seems to work OK. Please note that the Location_Copy or Location_Move cannot be subfolders of Location_Source.
Please let me know if it works for you.
Cheers,
Vlad
 

Attachments

  • FileWorkflowConceptVlad3.zip
    29.9 KB · Views: 18

Sampoline

Member
Local time
Today, 12:00
Joined
Oct 19, 2020
Messages
161
Hi,
Here is the revised copy, just tested it and seems to work OK. Please note that the Location_Copy or Location_Move cannot be subfolders of Location_Source.
Please let me know if it works for you.
Cheers,
Vlad
Hey Vlad,
Thanks this is working now. Just tested it on my end and all is working out great as I required. Cheers for that. And yes the Location_Copy and Location_Move are in different locations. So shouldn't be an issue. Thankyou!
 

Users who are viewing this thread

Top Bottom