Open_Files_In_A_Directory

[Ron]

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]

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 here:


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]

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