Selecting a specific file from many

[Bishop]

I have the following code:

Option Explicit

Sub Consolidate()
Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim Fnum As Long
Dim mybook As Workbook
Dim CalcMode As Long
Dim sh As Worksheet
Dim ErrorYes As Boolean
Dim DCLastRow As Integer 'DirectorCopy
Dim MCLastRow As Integer 'Monthly Compiler
Dim CMonth As String 'Compile Month
Dim CYear As String 'Compile Year
Dim Month As Integer

Dim center(18) As String
center(1) = "Bardstown"
center(2) = "Bothell"
center(3) = "VCollinsville"
center(4) = "El Paso"
center(5) = "Evansville"
center(6) = "Greensboro"
center(7) = "VHeathrow"
center(8) = "Joplin"
center(9) = "Kennesaw"
center(10) = "Lafayette"
center(11) = "Malvern"
center(12) = "VManhattan"
center(13) = "VMansfield"
center(14) = "VOttawa"
center(15) = "VPonco City"
center(16) = "VReno"
center(17) = "VSioux City"
center(18) = "VTerra Haute"

Dim FileCount As Long
Dim ScoringAve As Double
Dim i As Long

' If Cells(13, 4).Value = "January" Then Month = 1
' If Cells(13, 4).Value = "February" Then Month = 2
' If Cells(13, 4).Value = "March" Then Month = 3
' If Cells(13, 4).Value = "April" Then Month = 4
' If Cells(13, 4).Value = "May" Then Month = 5
' If Cells(13, 4).Value = "June" Then Month = 6
' If Cells(13, 4).Value = "July" Then Month = 7
' If Cells(13, 4).Value = "August" Then Month = 8
' If Cells(13, 4).Value = "September" Then Month = 9
' If Cells(13, 4).Value = "October" Then Month = 10
' If Cells(13, 4).Value = "November" Then Month = 11
' If Cells(13, 4).Value = "December" Then Month = 12
' CMonth = MonthName(Month, True)
'This one line of code replaces the above 13 lines
CMonth = Left(Cells(13, 4).Value, 3)
CYear = Right(Cells(13, 7).Value, 2)


'Fill in the path\folder where the files are
MyPath = "X:\C&A Analysts Team\PF Process\1 Tally & PF's Work in
Progress\Centers\"

For i = 1 To 18

' 'Add a slash at the end if the user forget it
' If Right(MyPath, 1) <> "\" Then
' MyPath = MyPath & "\"
' End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & center(i) & "\*" & CMonth & CYear &
"*.xl*")

If FilesInPath = "" Then
MsgBox "No files found in " & center(i)
GoTo ContinueLoop
End If

If FilesInPath <> "" Then
FileCount = FileCount + 1
End If

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop

All the files in these folders will be in the same format:

Center C&A PF Month Year Week Initials

So here's a sample of some files I'm working with:

VCollinsville C&A PF May 09 wk1 lb.xlsm
El Paso C&A PF Jun 09 wk3 gh.xlsm
Bardstown C&A PF Jul wk2 bm.xlsm

Here is a sample of the files found in one particular folder:

Bothell C&A PF May 09 wk4 gh
Bothell C&A PF Jul 09 wk2 gh
Bothell C&A PF Jul 09 wk5 gh
Bothell C&A PF Jun 09 wk3 gh

With this line of code:

FilesInPath = Dir(MyPath & center(i) & "\*" & CMonth & CYear & "*.xl*")

I'm attempting to isolate files that have a specific Month and Year. So say
I only want July 09 files. From the list above I would only be pulling:

Bothell C&A PF Jul 09 wk2 gh
Bothell C&A PF Jul 09 wk5 gh

But FilesInPath sees the first file, which means it's not "" and proceeds to
put all 4 files in the array. How can I isolate only the files that qualify
under the Month/Year criteria and put only those files in the array?

 

[OssieMac]

Hi Bishop,

I think you are missing a space between cmonth and cyear in the concatenated
string.
Try creating the concatenated string in a string variable and then place a
stop in the code after the concatenation and when the code stops, select
debug and hover the cursor over the variable and see what it is returning.

myFilter = MyPath & center(i) & "\*" & CMonth & " " & CYear & "*.xl*"
Stop
FilesInPath = Dir(myFilter)

 

[Per Jessen]

Hi

Try this:

---Cut
'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
If InStr(1, FilesInPath, CMonth & " " & CYear,
vbTextCompare) Then
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
End If
Loop
---Cut---


Hopes this helps.
....
Per

 

[Bishop]

I did what you asked. This is what's happening now:

"X:\C&A Analysts Team\PF Process\1 Tally & PF's Work in
Progress\Centers\Bardstown\* *.xl*"

So it looks like CMonth and CYear aren't showing up for some reason. I added:

Dim MonthFilter As String

And I have the following code:

CMonth = Left(Cells(13, 4).Value, 3)
CYear = Right(Cells(13, 7).Value, 2)

MyPath = "X:\C&A Analysts Team\PF Process\1 Tally & PF's Work in
Progress\Centers\"

MonthFilter = MyPath & center(i) & "\*" & CMonth & " " & CYear &
"*.xl*"
FilesInPath = Dir(MonthFilter)

What I need is:

"X:\C&A Analysts Team\PF Process\1 Tally & PF's Work in
Progress\Centers\Bardstown\* Aug 09 *.xl*"

 

[Bishop]

Ok, nevermind... apparently the correct workbook wasn't active... which is
odd really. The active workbook should have been Monthly Compiler. My plan
is to assign this macro to a button in that workbook. So if I run the macro
from a button in the Monthly Compiler workbook is there any reason I should
have to put a line of code in the macro that makes sure Monthly Compiler is
the active workbook before it runs?