Solved

Compare date modfied when copying files-MS Access VBA

Posted on 2012-03-29
10
423 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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
 
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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

740 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