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", _
If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1
'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
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question