VBA code to search files based on last modified date

yogeshtalks · Sep 21st 2011

Hi all,

Another query regarding searching of files. This topic in VBA is not easy, at least to my standards.

This time, I want to search a folder which contains hundreds of data files. I want to search for those files based on the Last modified date/time. I want to search those files and copy them to a different destination and open them to copy a particular range of cells to another new work sheet for processing.

For example: I want to search for those files that were last saved in a particular month/week.

The code i need is to search and copy files at destination folder based on date range that I specify and open them. I hope that I can manage with the rest of the code.
I would like to input the date range at a particular cell in my worksheet which the code can access to perform the search.

Thanks in advance.

Cheers
-Yogesh

smuzoen · Sep 22nd 2011

Re: VBA code to search files based on last modified date

You will need to use the Microsoft Scripting Runtime Library - what that means is you need to add a reference to your VBA code. In the VBA editor add a reference (Tools, Add Reference) and look for Microsoft Scripting Runtime Library and tick its box. The following code will show you the various attributes you can then determine from the macro.

Code

Sub ListFilesInFolder()




Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder("c:\temp") ' Place in here the directory of interest
    For Each FileItem In SourceFolder.Files
        ' display file properties
       MsgBox FileItem.Path & FileItem.Name
       MsgBox FileItem.Size
       MsgBox FileItem.Type
       MsgBox FileItem.DateCreated
       MsgBox FileItem.DateLastAccessed
       MsgBox FileItem.DateLastModified
       MsgBox FileItem.Attributes
       MsgBox FileItem.ShortPath & FileItem.ShortName
    Next FileItem

Display More

In your case I would guess DateLastModified is your important variable.

Code

FileItem.Copy "C:\temp1\" & FileItem.Name, True 'To copy the selected file use
Application.Workbooks.Open (FileItem.Name) ' To open the workbook

To convert the DateLastModified to a date use

Code

dateString = Format(FileItem.DateLastModified, "dd/mm/yy")

You said you want to get a date from a workbook - you can compare this date to the value from the worksheet and determine if it is a file you want to copy/open. This will get you on your way - if you need more help just ask

yogeshtalks · Sep 22nd 2011

Re: VBA code to search files based on last modified date

Oh boy!!! It looks like a bit too advanced stuff for me. Anyhow, let me see if I can work with it.

thanks a lot for your help.

yogeshtalks · Sep 22nd 2011

Re: VBA code to search files based on last modified date

Thank you so much Smuzoen. It works great. I cannot believe its working for me.

It wasn't that complicated as I imagined. You guys are doing great help.

Cheers
Yogesh

yogeshtalks · Sep 22nd 2011

Re: VBA code to search files based on last modified date

I just happened to notice one thing.

After i had performed the search and did the necessary operations on those files, the last updated date/time changes to the current time (somewhat like i had saved the files again which i did not).

Is there a way to get rid of this. I want to keep the original files as it is with the same date/time.

Thanks
Yogesh

smuzoen · Sep 23rd 2011

Re: VBA code to search files based on last modified date

With your code you found the file, copied it to a location and then

Quote

did the necessary operations on those files

- what exactly does that mean? Is the date last saved on the original files changed (or date last modified?) - are the original file attributes changing? You did not open the original files at all? Sorry for my confusion - could you be very specific about what attributes etc changes on what files.

yogeshtalks · Sep 23rd 2011

Re: VBA code to search files based on last modified date

My apologies for the confusing post.

what I meant to say was, the original files are copied to the new destination folder but, i guess the code opens the files from the original location rather than opening the copy at the destination folder.

Quote

FileItem.Copy "C:\temp1\" & FileItem.Name, True 'To copy the selected file use Application.Workbooks.Open (FileItem.Name) ' To open the workbook

As seen above, the files are copied, but still the original files are opened. I may be wrong, but thats my understanding.
But, I managed to tweak the code a little bit to so that after copying the files, the files from the destination folder are opened to perform the operations on it.
Now, it works fine.
Thanks a lot for your guidance.

Cheers
Yogesh

smuzoen · Sep 24th 2011

Re: VBA code to search files based on last modified date

It is fantastic when you can work out the answer yourself. Well done!!

VBA code to search files based on last modified date

Participate now!

Share

Users Viewing This Thread