Hi Dave, thanks for your assistance. It's working great as planned.
At first I didn't understand what I needed to do, but after looking at
for awhile I was able to figured it out. I also thank Rick, your
assistance is greatly appreciated, Ron

On May 19, 4:26*pm, Dave Peterson wrote:
I've never had good luck getting users to type in the correct abbreviation for
the month. *And I don't want to validate all the ways they could type the entry.

Instead, I'll ask them for a date in that month and let excel verify that it's a
date. *

In fact, it may be best to use a calendar control to get the date from the
user. *Then you don't have to worry about them entering:

01/02/03
and not having any definitive date (Jan 2, 2003 or 1 feb 2003 or ...)

Ron de Bruin shares some notes on how to use a calendar control he

This code uses application.inputbox with type:=1. *That means that the user has
to type a number. *And dates are just numbers to excel. *Then it does some minor
validity/sanity checks that you'll want to fix for your project.

Option Explicit
Sub testme01()

* * Dim WhichDate As Long
* * Dim WhichMonth As Long
* * Dim fPath As String
* * Dim CurrentMonthAbbreviation As String

* * WhichDate = Application.InputBox(Prompt:="Enter the date to use", _
* * * * * * * * * * Default:=Format(Date, "mmmm dd, yyyy"), Type:=1)

* * If WhichDate = 0 Then
* * * * 'user hit cancel
* * * * Exit Sub
* * End If

* * If Year(WhichDate) < 2000 _
* * *Or Year(WhichDate) 2015 Then
* * * * MsgBox "Please enter a nicer date"
* * * * Exit Sub
* * End If

* * WhichMonth = (Month(WhichDate) + 12 - 1 - 3) Mod 12 + 1

* * CurrentMonthAbbreviation = Format(WhichMonth, "00") _
* * * * * * * * * * * * * * * * * * & " " & Format(WhichDate, "mmm")

* * fPath = "T:\Budget Reports\NAPO\National Parts Operations\" _
* * * * * * * * & CurrentMonthAbbreviation & "\"

* * MsgBox fPath

End Sub





Ron wrote:

Hello all, *I got help from Rick Rothstein with this code. *It works
great however, in the line
fPath = "T:\Budget Reports\NAPO\National Parts Operations\01 " & _
* * * * * * * *Left(CurrentMonthAbbreviation, 3) & "\"

When the month changes so does the leading 01. *01 Apr is the first
month of our fiscal year. *The directories range from 01 Apr to 12
Mar. *Is there a way for the input box to ask for the full directory
name (i.e. "02 May" and so on)?
Thank you for your assistance, Ron

Sub Open_Files_In_A_Directory()
* * Dim fileList() As String
* * Dim fName As String
* * Dim fPath As String
* * Dim i As Integer
* * Dim CurrentMonthAbbreviation As String

* * 'define the directory to be searched for files
* * 'fPath = "T:\Budget Reports\NAPO\National Parts Operations\01 Apr
\"
CurrentMonthAbbreviation = InputBox("Enter the Month to Open" &
Chr(13) & "Use the 3 Letter Abbreviation (i.e. Sep)")
fPath = "T:\Budget Reports\NAPO\National Parts Operations\01 " & _
* * * * * * * *Left(CurrentMonthAbbreviation, 3) & "\"

* * 'build a list of the files
* * fName = Dir(fPath & "*.xls")
* * While fName < ""
* * * * 'add fname to the list
* * * * i = i + 1
* * * * ReDim Preserve fileList(1 To i)
* * * * fileList(i) = fName
* * * * 'get next filename
* * * * fName = Dir()
* * Wend

* * 'see if any files were found
* * If i = 0 Then
* * * * MsgBox "No Files Found!" & Chr(13) & "Are you sure the correct
month was entered?"
* * * * Exit Sub
* * End If

* * 'cycle through the list and open
* * 'just those with the letter DP in the filename
* * 'instr the following way is a case insensitive test
* * For i = 1 To UBound(fileList)
* * * * If InStr(1, fileList(i), "DP", 1) 0 Then
* * * * * * Workbooks.Open fPath & fileList(i)
* * * * End If
* * Next
End Sub

--

Dave Peterson- Hide quoted text -

- Show quoted text -