Type Mismatch
 

I have the following code:

Sub Consolidate()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String, 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*")

FilesInPath = Dir(MyPath \ Center(i) & "*.xl*") is giving error 13 Type
Mismatch. What am I doing wrong?

Type Mismatch
 

Bishop wrote:
I have the following code:

Sub Consolidate()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String, 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*")

FilesInPath = Dir(MyPath \ Center(i) & "*.xl*") is giving error 13 Type
Mismatch. What am I doing wrong?

Probably just a little programmer's moment with the backslash...

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

Type Mismatch
 

A moment, indeed. That was it. Thanks!

"smartin" wrote:

Bishop wrote:
I have the following code:

Sub Consolidate()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String, 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*")

FilesInPath = Dir(MyPath \ Center(i) & "*.xl*") is giving error 13 Type
Mismatch. What am I doing wrong?

Probably just a little programmer's moment with the backslash...

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