Solved

Compare date modfied when copying files-MS Access VBA

Posted on 2012-03-29
10
430 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 48

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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 48

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 48

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

689 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