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