0

Could someone tell me if it is possible for a user to input two seperate dates into input boxes and then search a folder for files with (ideally) create dates that fall between the input dates?

I can do a search through files in a folder fine but the number of files is increasing every day and the time to run a search through all of them is getting longer. I'm hopeing that if the user can select a date range then this will cut down the time to run.

If that isn't possible at all is it possible to set a macro to search through files in a folder STARTING with the most recently created and then working back from there?

Sub UKSearch()

 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 'Search function to find specific consignment number from multiple intake sheets'
 'Used by Traffic Office                                                         '
 'Created by *********** 11/03/14     Password to unlock = *********             '
 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim FSO As Object 'FileSystemObject
Set FSO = CreateObject("scripting.filesystemobject")
Dim Directory As String
Dim FileName As String
Dim varCellvalue As Long

Application.ScreenUpdating = False
MsgBox ("This may take a few minutes")

'value to be searched
varCellvalue = Range("D13").Value

'Change the directory below as needed
Directory = "\\*******\shared$\Common\Returns\*********\"
If Right(Directory, 1) <> "\" Then
   Directory = Directory & "\"
End If

'Search for all files in the directory with an xls* file type.
FileName = Dir(Directory & "*.xls*")

''''''''''''''''''''''''
 'Opens, searches through and closes each file
 Do While FileName <> ""
 OpenFile = Directory & FileName
 Workbooks.Open (OpenFile)

Workbooks(FileName).Activate

'Count through all the rows looking for the required number
ActiveWorkbook.Sheets("UK Scan Sheet").Activate
LastRow = Range("B65536").End(xlUp).Row

intRowCount = LastRow

Range("B1").Select

For i = 1 To intRowCount
    'If the required number is found then select it and stop the search
   If ActiveCell.Value = varCellvalue Then
       GoTo Finish
       Else
   End If
ActiveCell.Offset(1, 0).Select
Next i

Workbooks(FileName).Close
FileName = Dir
OpenFile = ""
 Loop
 ''''''''''''''''''''''''''

Finish:

Application.ScreenUpdating = False

End Sub
LuckySevens
  • 333
  • 3
  • 7
  • 22
  • I'm not sure of what you are aiming at, but if you are trying to get date filtered collection of file names you might get some inspiration from [this video](http://www.teachexcel.com/excel-tutorials/n-1566,VBA-Tips---List-Files-In-A-Folder.html)? – Hauns TM Mar 28 '14 at 09:04
  • you can look into this answer on how to determine creating date: http://stackoverflow.com/questions/18660818/excel-vba-pdf-file-properties/18661886#18661886 . Try to incorporate this in your code and if you get stuck, post back and we'll help you – Dmitry Pavliv Mar 28 '14 at 09:05
  • I'll try to be a little bit clearer:- What i would like to do is this - User presses command button - Input box for date (YYYY-MM-DD) - Input box for 2nd date (YYYY-MM-DD) - Input box for search value - Macro finds first file in a folder between the two input dates - Macro searches for search value in that file - If not found move to next file until reaching the last file between the two input dates. I hope that makes it a bit more clear what i would like to do? – LuckySevens Mar 28 '14 at 09:29
  • it's all clear, but you should show us what have you tried so far and where did you stuck. – Dmitry Pavliv Mar 28 '14 at 09:37
  • i added my code so far, as i say it just searches every file at the moment until it hits a match, i'd like to cut down the range of the files. – LuckySevens Mar 28 '14 at 09:46
  • before opening file, you can check date of creation like in this answer: http://stackoverflow.com/questions/18660818/excel-vba-pdf-file-properties/18661673#18661673 – Dmitry Pavliv Mar 28 '14 at 09:53
  • Wow thanks again for another answer that doesn't help at all... – LuckySevens Mar 28 '14 at 10:04
  • how exactly this doesn't help you? why it's not working for you? – Dmitry Pavliv Mar 28 '14 at 10:07
  • I'm not looking to return the dates from the files. The context is totally different. This should be such a simple question i had no idea it would be so much grief to find an answer. – LuckySevens Mar 28 '14 at 10:11

1 Answers1

2

Add to your dim section:

Dim oFile

Before your loop add:

Set oFile = CreateObject("Scripting.FileSystemObject")

In your loop before you open the file add an if statement:

if oFile.getFile(Directory & FileName).DateCreated >= EarliestDate and oFile.getFile(Directory & FileName).DateCreated <= LatestDate

You can also use oFile.getFile(Directory & FileName).DateLastModified - if you want to use the last change date of file instead of creation date.

ForteDevelop
  • 145
  • 8