Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a specific file from many
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? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting a specific worksheet | New Users to Excel | |||
Selecting specific cells | Excel Discussion (Misc queries) | |||
Selecting a specific tab in a tabstrip | Excel Programming | |||
Selecting Specific Sheets | Excel Programming | |||
Specific Cell Selecting | Excel Programming |