Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List of Files in A Directory | Excel Discussion (Misc queries) | |||
Files in a directory? | Excel Discussion (Misc queries) | |||
Load all files in a directory | Excel Discussion (Misc queries) | |||
Check if directory empty OR no of files in directory. | Excel Programming | |||
Open all files in a directory | Excel Programming |