Dir help

B

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*
 
D

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*")
 
J

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Type Mismatch 2
Exit Sub alternative 2
Selecting a specific file from many 4
.htm error 1
Subscript out of range 2
Application.Run error 2
Open CSV File 4
NEED HELP!!!! 1

Top