Importing .csv files into excel with VBA based on range of dates

[jcuoco78]

I need some help with VBA code to import multiple .csv files into a single Excel sheet. I have .csv files that get filenames based on when they were created, in the format ddmmyyyyhhmm (121220161306 for example). I am trying to create a userform to select a date range and have the selected range be used to find all filenames of the .csv files that meet those criteria but I can't begin to find a way to make dates selected with a date picker translate into the format of the filenames. The end goal is to pull all the data from the .csv files that fall within the date range selected into 1 sheet and then I will filter it and create graphs based on different scenarios. I have a good handle on the data analysis portion but right now I am just importing all .csv files in the folder location.

I do not want to have to import all files and filter out by dates because the number of .csv files will become extensive as days go by.

Any help would be greatly appreciated.

 

[JLGWhiz]

Lets say you want files from 1 November 2016 through 30 November 2016. Use 00:00 for the starting time and 12:00 for the ending time to create your UserForm dates. Assuming you use text boxes for user input, eg. tb1 and tb2. tb1 should show in Eor format 01/11/2016 00:00 and tb2 would show 30/11/2016 12:00

Dim stDt As String, fnDt As String
stDt = Format(CDate(tb1.Value), "ddmmyyyyhhmm")
fnDt = Format(CDate(tb2.Value), "ddmmyyyyhhmm")

Which should yield
stDt = "011120160000"
fnDt = "301120161200"
You can then Use

Sub t()
Dim fName As String, fPath As String, wb As Workbook
fPath = [whatever your folder path is]
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
fName = Dir(fPath & "*.csv")
    If fName <> ThisWorkbook.Name Then
        If Left(fName, 12) > stDt And Left(fName, 12) < fnDt Then 'finds workbooks within date range
            Do While fName <> ""
                Set wb = Workbooks.Open(fPath & fName)
                'Code to do your stuff here
                wb.Close [False to close without saving, True to save and close]
                fName = Dir
            Loop
        End If
    End If
End Sub

 

[jcuoco78]

Thanks for the bit of code. I am trying to get this to work now but it is finding the first file in the folder and it is out of range and it doesn't look for any other filenames in the folder. I tried to move the Do While Up between the first 2 If statements and that just keeps getting the same filename of the first file in the folder over and over. What am I missing?

 

[JLGWhiz]

Thanks for the bit of code. I am trying to get this to work now but it is finding the first file in the folder and it is out of range and it doesn't look for any other filenames in the folder. I tried to move the Do While Up between the first 2 If statements and that just keeps getting the same filename of the first file in the folder over and over. What am I missing?

This seems to be in order now. I did the other in a hurry and did not test it.

Sub t()
Dim fName As String, fPath As String, wb As Workbook, stDt As String, fnDt As String
stDt = "011120160000" 'rewrite these two variables to allow for varying dates.
fnDt = "301120162400"
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
fName = Dir(fPath & "*.csv")
    If fName <> ThisWorkbook.Name Then
        Do While fName <> ""
            If Left(fName, 12) > stDt And Left(fName, 12) < fnDt Then 'finds workbooks within date range
                Set wb = Workbooks.Open(fPath & fName)
                MsgBox "Hello"
                wb.Close [False to close without saving, True to save and close]
            End If
            fName = Dir
        Loop
    End If
End Sub

 

[jcuoco78]

That worked perfect thanks!