Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Compare date modfied when copying files-MS Access VBA

Posted on 2012-03-29
10
Medium Priority
?
462 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 49

Accepted Solution

by:
Dale Fye earned 500 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
 

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 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
 
LVL 49

Expert Comment

by:Dale Fye
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 49

Expert Comment

by:Dale Fye
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

571 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