Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro to moves files based on a period

    I have tried to modify code as to move files less that a certain period from C:\Tax Files to C:\old tax files

    For eg moving all files in the folder C:\Tax Files < 01/10/2014 to C:\old tax files. Existing files in C:\old tax files can be overwritten

    It would be appreciated if someone could please assist me in resolving this
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Howard,

    The following code will examine the date created and date modified attributes of the files in the "Tax Files" folder. If either is older than 1/10/2014 then the files will be moved to the "old tax files" folder. Both folders must be present and no duplicate files already residing in the "old tax files" folder. Let me know if you want to check both attributes or just one or the other.

    Maud

    Code:
    Public Sub MoveFiles()
    On Error Resume Next
    Application.ScreenUpdating = False
    '--------------------------------
    'DECLARE AND SET VARIABLES
    Dim FSO As Object, ShellApp As Object
    Dim File As Variant, Myfolder
    Dim Path, DestPath
    Set FSO = CreateObject("scripting.filesystemobject")
    Set ShellApp = CreateObject("Shell.Application")
    Path = "C:\Tax Files\"
    DestPath = "C:\old tax files\"
    Set Myfolder = ShellApp.Namespace(Path)
    '-------------------------------------------------
    'GET FILE INFORMATION
    For Each File In Myfolder.Items
        MsgBox Myfolder.GetDetailsOf(File, 4)
        MsgBox Myfolder.GetDetailsOf(File, 3)
        If Myfolder.GetDetailsOf(File, 4) < CDate("01/10/2014") Or _
            Myfolder.GetDetailsOf(File, 3) < CDate("01/10/2014") Then
            FSO.MoveFile Source:=Path & File, Destination:=DestPath
        End If
    Next
    '-------------------------------------------------
    'CLEANUP
    Set Myfolder = Nothing
    Set ShellApp = Nothing
    Set FSO = Nothing
    End Sub
    Last edited by Maudibe; 2015-08-08 at 10:50.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maud

    It is the date the file was created/modified

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    solution above post #2

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maud

    No files have moved to C:\old tax files folder. When Running the macro , all I get is a pop up menu showing the date and time

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Howard,

    The message boxes were there for testing. You can comment them out. Before you do, however, are any of the message boxes displaying dates earlier than 1/10/2014?

    Code:
        
        'MsgBox Myfolder.GetDetailsOf(File, 4)
        'MsgBox Myfolder.GetDetailsOf(File, 3)


    Make sure your folders match those of the code:

    Code:
    Path = "C:\Tax Files\"
    DestPath = "C:\old tax files\"
    If you still get nothing, comment the code line On error resume next then re-run the code. Let me know if you get an error.

    Maud

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maud

    I commented the line code On error resume next



    My dates are dd/mm/yyyy Regional UK. Has this not got anything to do with it as American dates are mm/dd/yyyy?

    When running the Code , I get "File not found" and the code below is highlighted

    Code:
     FSO.MoveFile Source:=Path & File, Destination:=DestPath
    I have dates prior to Oct 2014 appearing in the message box (01/06/2014) - 1 June 2014 (dd/mm/yyyy) etc

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    I am sure it does make a difference using different date formats. Perhaps you can refer to the code that zeddy provided you in your sample workbook. He would be more familiar with the method he used.

    Maud
    Last edited by Maudibe; 2015-08-09 at 09:23.

  9. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks Maud

    I will attempt to get this sorted out, otherwise will ask Zeddy for some help

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Howard

    It can be tricky sometimes when dealing with dates and different date formats in different regions.
    I appreciate Maud's help in tackling your request.

    I have prepared a file which will hopefully work in any region (I would be grateful for others to test this).
    See attached file. (This is for Excel2007 onwards).

    This Tool will allow you to choose your source and destination folders, a file mask to choose what types of files or filenames to move (e.g. VAT*.xls*, H*.pdf, 2015*.doc, fred*.xls etc etc), and a cutoff date for moving the files.

    The Tool checks that you have entered valid data for these required items.

    To avoid the entry of unrecognised dates (e.g. "banana"), I have added a calendar for selecting a date. I seem to recall that VAT periods are sometimes quarterly, so my calendar displays a 3-month panel to choose your date from the Form.

    I have found that when using multiple monitors, some Forms are not displayed in the same window as the Excel you are using, so I have added code that deals with that i.e. will show the calendar on the same screen as this Tool.

    I have run some quick tests with this tool and it seems to work OK. I haven't checked what happens if a file that is 'marked' for moving is currently 'open' or 'locked'. A file list of 'matching files' is generated, based on the file mask specified. A 'status' column shows whether the file was actually moved or not.

    Please let me know if this does what you want.

    zeddy
    •Excel Very Lovely Teamaker
    .
    Attached Files Attached Files

  11. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2015-08-13)

  12. #11
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks very much, for all your effort. The code moves all zip files earlier than the period selected in the source folder, but not the zipped files in the sub-folders for eg I have sub- folders within C:\tax that have not moved C:\tax\Br1 C:\Tax\br3 etc

    It would be appreciated if you would kindly amend to include sub-folders when moving files earlier than a certain period

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Howard

    ..you could always just change the source folder to the next subfolder, while keeping the destination folder the same!

    OK, if you have lots of subfolders, then that could be tedious.
    But then, do you really want to 'move' files from lots of subfolders into a single folder???
    What happens to files with duplicate names in the source subfolders?? Which one gets 'saved'??

    I reckon if your source folder has subfolders, then maybe your destination folder should also have similar corresponding subfolders. But then it gets more complicated. How do you make sure this is the case?
    What you are asking for can be done of course, but I think the purpose of the forum is to help and guide rather than just deliver.

    zeddy
    •Excel Antiquities Valuer
    .

  14. #13
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy


    Thanks for your reply. I have 16 subfolders, but none of the files have duplicate names so no need for destination folder to have subfolders. It is used for archiving purposes only

  15. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Howard

    ..I'll add the option to 'include' subfolders within the source folder, and will post back here when tested and finished, unless someone else beats me to it.

    I'll have to generate some test files and test subfolders etc etc. I'm having a weekend away so will work on this when I get back.

    zeddy
    •Excel Scottish Clandestine Clan

  16. #15
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the reply. Thanks for your willingness to amend the code to include subfolders within the source folder

    Enjoy your weekend getaway

    Howard

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •