opening order macro for files in a folder
 

I've setup a pretty neat macro that opens 50+ csv file and then copies the
range (A28:B128) consecutively underneath each other but I'm worried that I
have no control over the order in which these files are being opened/inserted
underneath each other...I want to choose the opening order based on the file
name only (either a-z or z-a)..knowing how to order an open by date is also
very helpful to me....thank you in advance......Here's the opening macro I've
been using:

Sub monthly_reports_opens_change_dir_()
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim rnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant

SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings\May 2006"
ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="csv Files (*.csv),
*.csv", _
MultiSelect:=True)
If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1
basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet

For N = LBound(FName) To UBound(FName)
Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets(1).Range("A28:B128")
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells(rnum, "B")

basebook.Worksheets(1).Cells(rnum, "A").Value = mybook.Name
' This will add the workbook name in column D if you want

sourceRange.Copy destrange
' Instead of this line you can use the code below to copy only
the values

' With sourceRange
' Set destrange = basebook.Worksheets(1).Cells(rnum,
"A"). _
' Resize(.Rows.Count, .Columns.Count)
' End With
' destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
Next
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

opening order macro for files in a folder
 

Hi Sanbitter Man

You can fill a array with the file names you select and sort the array
On my ADO page there is a example and function
http://www.rondebruin.nl/ado.htm#files


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Sanbitter Man" <Sanbitter wrote in message
...
I've setup a pretty neat macro that opens 50+ csv file and then copies the
range (A28:B128) consecutively underneath each other but I'm worried that I
have no control over the order in which these files are being opened/inserted
underneath each other...I want to choose the opening order based on the file
name only (either a-z or z-a)..knowing how to order an open by date is also
very helpful to me....thank you in advance......Here's the opening macro I've
been using:

Sub monthly_reports_opens_change_dir_()
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim rnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant

SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings\May 2006"
ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="csv Files (*.csv),
*.csv", _
MultiSelect:=True)
If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1
basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet

For N = LBound(FName) To UBound(FName)
Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets(1).Range("A28:B128")
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells(rnum, "B")

basebook.Worksheets(1).Cells(rnum, "A").Value = mybook.Name
' This will add the workbook name in column D if you want

sourceRange.Copy destrange
' Instead of this line you can use the code below to copy only
the values

' With sourceRange
' Set destrange = basebook.Worksheets(1).Cells(rnum,
"A"). _
' Resize(.Rows.Count, .Columns.Count)
' End With
' destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
Next
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub