Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dir help
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* |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dir help
It looks like:
FilesInPath = Dir(MyPath & center(i) & "*.xl*") should be: FilesInPath = Dir(MyPath & center(i) & "\*.xl*") or FilesInPath = Dir(MyPath & center(i) & "\" & "*.xl*") 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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dir help
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 "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 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* |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dir help
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 --------------- Jacob Skaria "Jacob Skaria" wrote: 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 "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 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* |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|