Solved

Compare date modfied when copying files-MS Access VBA

Posted on 2012-03-29
10
373 Views
Last Modified: 2012-04-06
I'm using the following code to copy all files from the Source folder to the Destination folder:

Dim sourceLocation As String
Dim destinationLocation As String
Dim fso
   
sourceLocation = "E:\Source\*.*"
destinationLocation = "E:\Destination\"

Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile sourceLocation, destinationLocation

I have no issues with the file copy.  However, if a file contained in the Source folder already exists in the Destination folder,  I would like to be able to compare the Date Modified attribute of files.  In the end, if the file exists in both folders, I want to have the most current version of file in the Destination folder.

Hopefully this makes sense.

Any help is appreciated.
0
Comment
Question by:giswest
  • 4
  • 3
  • 3
10 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 125 total points
ID: 37783189
You will not be able to do it with the wildcard file names.  In order to do that, you will need to loop through the files and folders in your source folder, then check to see whether those files exist in the destination folder, and if so, compare the ModifiedDate property of the file.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37783638
0
 

Author Comment

by:giswest
ID: 37787254
Based on both of your suggestions, I think that I'm pretty close to getting this worked out.  The only thing that I'm hung up on is the Date Modifed comparison for files that exist in both the Source and Destination folders.

The following code is what I have so far.  The lines of concern are highlighted in bold.

Private Sub Command0_Click()

Dim sourceFiles
Dim sourceLocation As String
Dim destinationLocation As String
Dim objectFile
Dim destinationFile
Dim fso
 
sourceLocation = "C:\Documents and Settings\GIS Admin\Desktop\Delete\Source\"
destinationLocation = "C:\Documents and Settings\GIS Admin\Desktop\Delete\Destination\"

Set fso = CreateObject("Scripting.FileSystemObject")
Set sourceFiles = fso.GetFolder(sourceLocation).Files

For Each objectFile In sourceFiles

    If Not fso.FileExists(destinationLocation & objectFile.Name) Then
   
            objectFile.Copy destinationLocation & objectFile.Name
           
    ElseIf fso.FileExists(destinationLocation & objectFile.Name) Then
   
       destinationFile = destinationLocation & objectFile.Name  
 
        If objectFile.DateLastModified > destinationFile.DateLastModfied Then        

            objectFile.Copy destinationLocation & objectFile.Name
           
        End If
       
    End If

Next

Set sourceFiles = Nothing
Set fso = Nothing

End Sub
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 125 total points
ID: 37787725
<The only thing that I'm hung up on is the Date Modifed comparison for files that exist in both the Source and Destination folders.>
Please explain in detail what you mean by "Hung Up"...


If it were me, I would declare both files as distinct variables.
filFileToCopy (file to be possibly copied)
filExistingFile (existing file)
...just to make it clearer as you read through the code...
(Obviously you have to reset these variables with each file loop...)

Then do something *roughly* like this:
If filFileToCopy.DateLastModified >filExistingFile.DateLastModified Then
    filFileToCopy.Copy destinationLocation & filFileToCopy.Name
End if

Note that this does not explicitly allow for situations where the DateModified is the same (or the existing file data is greater)...
In those cases, nothing should happen any way ...
(But depending on what you want, you may have to add those condition into the If-Then-Else, and do whatever you want...)

...I am sure you see where I am going with all of this...

...Any clearer...?

;-)

JeffCoachman
0
 

Author Comment

by:giswest
ID: 37788156
Jeff,

The "Hang up" is the Run-time error '424': Object Required error I recieve when I run the code.  I know that the error is associated with the destinationFile.DateLastModfied portion, because when I replace destinationFile.DateLastModfied with a date (see below) everything works fine.

Replaced: If objectFile.DateLastModified > destinationFile.DateLastModfied Then
       
Replaced with: If objectFile.DateLastModified > "3/30/2012 11:00 AM" Then

Am I not declaring and/or defining the variable properly?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37788200
Post an updated copy of your code for us to review.
0
 

Author Comment

by:giswest
ID: 37788355
My current code is as follows.

Dim sourceFiles
Dim sourceLocation As String
Dim destinationLocation As String
Dim objectFile
Dim destinationFile
Dim fso
 
sourceLocation = "C:\Documents and Settings\GIS Admin\Desktop\Delete\Source\"
destinationLocation = "C:\Documents and Settings\GIS Admin\Desktop\Delete\Destination\"

Set fso = CreateObject("Scripting.FileSystemObject")
Set sourceFiles = fso.GetFolder(sourceLocation).Files

For Each objectFile In sourceFiles

    If Not fso.FileExists(destinationLocation & objectFile.Name) Then
   
            objectFile.Copy destinationLocation & objectFile.Name
           
    ElseIf fso.FileExists(destinationLocation & objectFile.Name) Then
   
        destinationFile = destinationLocation & objectFile.Name
       
        If objectFile.DateLastModified > destinationFile.DateLastModfied Then
            objectFile.Copy destinationLocation & objectFile.Name
        End If
       
    End If

Next

Set sourceFiles = Nothing
Set fso = Nothing

As previously mentioned, everything works fine when I replace destinationFile.DateLastModfied with an actual date ("3/30/2012 11:00 AM" ).  Otherwise, I get the Run-time error '424': Object Required error  when I execute the code.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37788408
I don't normally use the FSO, but how about:

    set destinationFile = fso.  'then something else goes here
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37788552
You have some "Hanging" declarations there...

Dim sourceFiles
Dim objectFile
Dim destinationFile
Dim fso

Whenever you have Dim, you really need to specify the "As"
(ex: Dim fso As FileSystemObject) else the variable becomes a "variant", or Access cannot figure out what it is...

So you may just need:
Dim destinationFile As String
...or possibly
Dim destinationFile As File
0
 

Author Closing Comment

by:giswest
ID: 37817521
Sorry for not responding sooner, but I've been extremely busy.

I was able to take the suggestions that you both made to generate a partial solution to the issue I was facing.  I appreciated your help.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now