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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a specific file from many
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) -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a specific file from many
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 On 4 Aug., 23:26, Bishop wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a specific file from many
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*" "OssieMac" wrote: 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) -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a specific file from many
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? "Bishop" wrote: 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*" "OssieMac" wrote: 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) -- Regards, OssieMac |
Reply |
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 |