Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Compare date modfied when copying files-MS Access VBA

Posted on 2012-03-29
10
Medium Priority
?
451 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 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 48

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 48

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

610 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