Add date range for file retrieval

[sizzi09]

Hi,
I am currently using this programming to pull files into a workbook:
Option Explicit

Sub GetSheets()
Dim fPATH As String, fNAME As String, SheetName As String


Call PrepBy("userID", "Name", "DTime") 'notes who ran the macro



fPATH = Range("Directory").Text 'path to files - taken from Welcome sheet


If FileFolderExists(fPATH) = False Then
MsgBox "Folder does not exist, macro will exit"
Exit Sub
End If

fNAME = Dir(fPATH & "*.txt") 'get first filename from path
Do While Len(fNAME) > 0 'loop until no more filenames are found

ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
SheetName = Left(fNAME, Len(fNAME) - 4)
ActiveSheet.Name = SheetName

If Left(fNAME, 5) = "HOGAN" Then
Call ImportHogan(fPATH, fNAME) 'import rules for hogan files
Else
Call ImportIBS(fPATH, fNAME) 'import rules for IBS
End If
'Debug.Print fNAME
fNAME = Dir 'get next filename
Loop

Sheets("Welcome").Select

MsgBox "The files have been loaded"

End Sub

The directory that the macro is pulling from contains hundreds of files so the users are currently copying the files they want into a separate folder and then running the macro. Is there a way to cut out that step and replace it with the option for them to pull a range of files based on the "Date Modified"? Any help is appreciated! Thank you!

[Bernie Deitrick]

Set a reference in your project to Microsoft Scripting Runtime, and use this code:

Please Login or Register  to view this content.

[sizzi09]

Hi Bernie,
Thanks so much for responding. The programming looks great, the only edit I made was from "LastDateModified" to "DateCreated". My question now, is every time I run the macro it freezes my screen and I usually have to hit "end" to stop it from running. When I debug, I find that it gets hung up on " If strFileName Like "*.txt" Then ". Is there something else I need to edit to make it run faster? Or is it taking a significant amount of time because it is parsing through 7K+ files? Thanks again!

[Bernie Deitrick]

How many files do you need to actually import, out of the 7K+ files? Creating a worksheet for each can be an issue... and opening each file as a workbook takes time too. Memory requirements go up and speed goes down.

Try this version to get a message about how many you will be processing, and will show a progress message in the statubar:

Please Login or Register  to view this content.

[sizzi09]

Typically I will be importing 10-20 files. My previous programming, without the date range feature, would pull the files in less than a minute. I ran the new programming you provided and I keep getting "I will be importing 0 out of 7,000" files. I think I might be doing something wrong...but I cannot figure out what it is.

[Bernie Deitrick]

Here is the code as I tested it, since I do not have your PrepBy, ImportHogan, or ImportIBS subs - it looks for text files in the same folder as my test file: start by running it, then uncommenting the commented-out lines and re-running the code after deleting the newly created sheets.

Please Login or Register  to view this content.