Choose particular files from a directory
 

Hello,

Per the below, I'm trying to manipulate certain files within a directory.
As you can see in the last line, I am unsuccessfully trying to choose all
files in the directory entitled "Region***", as well as two specific files
("ABC.xls", "DEF.xls"). It works with just the Region, but not sure how to
get the syntax right to get the last two as well.....


TIA!




Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim SourceRcount As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir

MyPath = "Y:\Sales\2005"
ChDrive MyPath
ChDir MyPath

FNames = Array(Dir("REGION***.xls"), "ABC.xls", "DEF.xls")

Choose particular files from a directory
 

Sub ProcessFiles()

dim sPath

sPath=Dir("Y:\Sales\2005\REGION***.xls")

do while sPath<""
ProcessFile sPath
sPath=Dir()
loop

'process the other two files
ProcessFile "Y:\Sales\2005\ABC.xls"
ProcessFile "Y:\Sales\2005\DEF.xls"

end sub


Sub ProcessFile(sPath as string)
'process workbook code goes here
end sub


Tim.


--
Tim Williams
Palo Alto, CA


"JEFF" wrote in message
...
Hello,

Per the below, I'm trying to manipulate certain files within a directory.
As you can see in the last line, I am unsuccessfully trying to choose all
files in the directory entitled "Region***", as well as two specific files
("ABC.xls", "DEF.xls"). It works with just the Region, but not sure how
to
get the syntax right to get the last two as well.....


TIA!




Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim SourceRcount As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir

MyPath = "Y:\Sales\2005"
ChDrive MyPath
ChDir MyPath

FNames = Array(Dir("REGION***.xls"), "ABC.xls", "DEF.xls")

Choose particular files from a directory
 

Hi Jeff,

You're doing a few things wrong in your code:

(1) Fnames should be a variant - this is required by the Array function.
(See VBA help.)

(2) Just doing Dir("REGION***.xls") will only ever return one file, you
need all the files correct? SO you have to do a loop to get them all.

(3) No need to do ***, just one * will suffice. Or do you mean you just
want files with 3 characters after region - in which case you shoudl be
using "region???.xls".

I've just written this and noticed Tim has posted a much simpler
solution - however, you may find my corrections to your code so I'll
post anyway. Below I've included code that may help you elsewhere.

HTH,
Gareth

Sub DOThings()

Dim Fnames As Variant
Dim i as integer
COnst myPath as string = "Y:\Sales\2005"

Fnames = fcnGetFileList(myPath, "Region*.xls")

'Either add the remaining files to your array
'or process them separately.

For i = 0 To UBound(v)
Debug.Print myPath & "\" & Fnames(i)
Next i


End Sub

Function fcnGetFileList(ByVal strPath As String, Optional strFilter As
String) As Variant
' Returns a one dimensional array with just one empty blank element if
no files found
' Otherwise returns list of files

Dim myFile As String
Dim i As Integer
Dim FileList() As String

If strFilter = "" Then strFilter = "*.*"

'check in case we were already passed a \ or /
Select Case Right$(strPath, 1)
Case "\", "/"
strPath = Left$(strPath, Len(strPath) - 1)
End Select

ReDim Preserve FileList(0)

myFile = Dir$(strPath & "\" & strFilter)
Do While Len(myFile) 0
ReDim Preserve FileList(i) As String
FileList(i) = myFile
i = i + 1
myFile = Dir$()
Loop

fcnGetFileList = FileList

End Function


JEFF wrote:
Hello,

Per the below, I'm trying to manipulate certain files within a directory.
As you can see in the last line, I am unsuccessfully trying to choose all
files in the directory entitled "Region***", as well as two specific files
("ABC.xls", "DEF.xls"). It works with just the Region, but not sure how to
get the syntax right to get the last two as well.....


TIA!




Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim SourceRcount As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir

MyPath = "Y:\Sales\2005"
ChDrive MyPath
ChDir MyPath

FNames = Array(Dir("REGION***.xls"), "ABC.xls", "DEF.xls")