Patrick,
You can use FileDateTime(MyPath & "\" & TheFile) to get the creation/last
modified date.
- Add the file name and date to a 2-D array.
- Sort by the date.
- Loop through the array opening the filenames.
See below for code
As for opening files from a array:
Dim i as long
For i = LBound(YourArray) to UBound(YourArray)
Set wb = Workbooks.Open(MyPath & "\" & YourArray(i))
....etc
next
Wildcards in the Dir() can be :
<Help>In Microsoft Windows, Dir supports the use of multiple character (*)
and single character (?) wildcards to specify multiple files.</Help>
NickHK
Private Sub CommandButton1_Click()
Dim Folder As String
Dim FileData() As Variant
Dim FileTemp As String
Dim Counter As Long
Dim RetVal As Variant
Dim WB As Workbook
Const RedimBlock As Long = 10
Const DirToSearch As String = "C:\"
Const Pattern As String = "*.xls"
'Create initial elements
ReDim FileData(1 To 2, 1 To RedimBlock)
FileTemp = Dir(DirToSearch & "\" & Pattern)
Do While FileTemp <> ""
'Are all the elements full ?
If Counter = UBound(FileData, 2) Then
'Create another empty block to use
ReDim Preserve FileData(1 To 2, 1 To UBound(FileData, 2) +
RedimBlock)
End If
Counter = Counter + 1
'Store the data
FileData(1, Counter) = FileTemp
FileData(2, Counter) = FileDateTime(DirToSearch & "\" & FileTemp)
'Get the next file
FileTemp = Dir
Loop
'Remove any unused elements from FileData
ReDim Preserve FileData(1 To 2, 1 To Counter)
'Sort the array by the date
RetVal = Sort_TwoDimensionBubble(FileData, 2, 2)
'Now open and process each file
For Counter = LBound(FileData, 2) To UBound(FileData, 2)
Debug.Print FileData(2, Counter), FileData(1, Counter)
'Set WB = Workbooks.Open(FileData(1, Counter))
'process etc
'WB.Close False
Next
End Sub
'Code from
http://lsoron.free.fr/cd/vb/sources/biblio/sortbubble.htm
'
' Author:Gordon McI. Fuller
' Copyright:©2000 Force 10 Automation
' Created: Friday, March 17, 2000
'
Function Sort_TwoDimensionBubble(TempArray As Variant, _
Optional iElement As Integer = 1, _
Optional iDimension As Integer = 1, _
Optional bAscOrder As Boolean = True) _
As Boolean
Dim arrTemp As Variant
Dim i As Integer, j As Integer
Dim NoExchanges As Integer
On Error GoTo Error_BubbleSort
If iDimension = 1 Then
ReDim arrTemp(1, UBound(TempArray, 2))
Else
ReDim arrTemp(UBound(TempArray, 1), 1)
End If
'Loop until no more "exchanges" are made.
Do
NoExchanges = True
' Loop through each element in the array
If iDimension = 1 Then
For i = LBound(TempArray, iDimension) To UBound(TempArray,
iDimension) - 1
' If the element is greater than the element
' following it, exchange the two elements.
If (bAscOrder And (TempArray(i, iElement) > TempArray(i + 1,
iElement))) _
Or (Not bAscOrder And (TempArray(i, iElement) < TempArray(i + 1,
iElement))) Then
NoExchanges = False
For j = LBound(TempArray, 2) To UBound(TempArray, 2)
arrTemp(1, j) = TempArray(i, j)
Next j
For j = LBound(TempArray, 2) To UBound(TempArray, 2)
TempArray(i, j) = TempArray(i + 1, j)
Next j
For j = LBound(TempArray, 2) To UBound(TempArray, 2)
TempArray(i + 1, j) = arrTemp(1, j)
Next j
End If
Next i
Else
For i = LBound(TempArray, iDimension) To UBound(TempArray,
iDimension) - 1
' If the element is greater than the element
' following it, exchange the two elements.
If (bAscOrder And (TempArray(iElement, i) > TempArray(iElement,
i + 1))) _
Or (Not bAscOrder And (TempArray(iElement, i) <
TempArray(iElement, i + 1))) Then
NoExchanges = False
For j = LBound(TempArray, 1) To UBound(TempArray, 1)
arrTemp(j, 1) = TempArray(j, i)
Next j
For j = LBound(TempArray, 1) To UBound(TempArray, 1)
TempArray(j, i) = TempArray(j, i + 1)
Next j
For j = LBound(TempArray, 1) To UBound(TempArray, 1)
TempArray(j, i + 1) = arrTemp(j, 1)
Next j
End If
Next i
End If
Loop While Not (NoExchanges)
Sort_TwoDimensionBubble = True
On Error GoTo 0
Exit Function
Error_BubbleSort:
On Error GoTo 0
Sort_TwoDimensionBubble = False
End Function
Patrick said:
Thanks for all the help. Finally got it working
, one last question, is
there a way to open the files by their date saved OR how would I use an
array as the list to pull from??
Thanks again for ALL the help
Patrick
I am still confused as to what wildcard would work with my file names. I
have tried *.xls
----------CUT