Dir help

[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 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


'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) & "*.xl*")

FileInPath is showing "" all the time. Am I using Dir wrong? Why doesn't
my path show up like I want it to? For example, if i = 2 then FilesInPath
should look like:

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

 

[Dave Peterson]

It looks like:
FilesInPath = Dir(MyPath & center(i) & "*.xl*")

should be:
FilesInPath = Dir(MyPath & center(i) & "\*.xl*")
or
FilesInPath = Dir(MyPath & center(i) & "\" & "*.xl*")

 

[Jacob Skaria]

Dir(strPathandpartoffilename & "*.xl*") should retrieve the first xls file
found in the folder... Please check whether the folder is a valid folder

MyPath = "X:\C&A Analysts Team\PF Process\1 Tally & PF's Work in
Progress\Centers\"
Msgbox Dir(MyPath,vbDirectory)

(should return a dot. if the path is valid)

If this post helps click Yes

 

[Jacob Skaria]

Oops...I thought center(i) is part of file name. In that case it should be

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

If it is a folder name..refer Dave's post

If this post helps click Yes