Each day I download excel files that I name with a partial date format: e.g. 11.20_data or 11.21_data.

Currently my VBA code allows me to go and find each file and manipulate the content in it. However, I have to do that one by one. What I would prefer to do is tell VBA (perhaps with the use of an Inputbox) to perform the data manipulations based on a range of dates, rather than one at a time.

For example, say I have 3 days worth of data that I have to manipulate: 11.20 - 11.22 (20, 21, & 22). Can I create an input box that will allow me to specify that date range (or any range for that matter) and go to the first filename (11.20_data) manipulate it and then go to the next filename (11.21_data)? Based upon the number of days in the range, I want VBA to loop through the number of days and find each file and stop once it's done.

Set two variables to hold your dates. The use the TEXT() function in vba to feed the "modifed" date into the workbook.open command.


Option Explicit

Sub DatedFiles()
Dim firstDate As Date, secondDate As Date, Dt As Date
Dim wbData As Workbook, fName As String

firstDate = Application.InputBox("Enter the first date", "First Date", "11/20/2011", Type:=2)
If firstDate = 0 Then Exit Sub
secondDate = Application.InputBox("Enter the second date", "Second Date", "11/22/2011", Type:=2)
If secondDate = 0 Then Exit Sub

For Dt = Application.Min(firstDate, secondDate) To Application.Max(firstDate, secondDate)
fName = "C:\MyDataFiles\" & Format(Dt, "MM.DD") & "_data"
If Len(Dir(fName)) > 0 Then
'open file and process
Set wbData = Workbooks.Open(fName)
'do stuff here


wbData.Close False
Else
MsgBox "The date file: " & fName & " could not be found. Please make a note"
End If
Next Dt

End Sub