Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open files in order
I am trying to modify Ron de Bruins code to open all files in a directory.
Sub Example2() Dim MyPath As String Dim FilesInPath As String Dim MyFiles() As String Dim SourceRcount As Long Dim Fnum As Long Dim mybook As Workbook Dim basebook As Workbook Dim sourceRange As range Dim destrange As range Dim rnum As Long 'Fill in the path\folder where the files are 'MyPath = "C:\Data" or on a network : MyPath = "\\ComputerName\YourFolder" 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*.xls") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If On Error GoTo CleanUp Application.ScreenUpdating = False Set basebook = ThisWorkbook 'clear all cells on the first sheet basebook.Worksheets(1).Cells.Clear rnum = 1 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop 'Loop through all files in the array(myFiles) If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum)) Set sourceRange = mybook.Worksheets(1).range("A1:C1") SourceRcount = sourceRange.Rows.Count Set destrange = basebook.Worksheets(1).range("A" & rnum) ' This will add the workbook name in column D if you want basebook.Worksheets(1).Cells(rnum, "D").Value = mybook.Name 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 rnum = rnum + SourceRcount mybook.Close savechanges:=False Next Fnum End If CleanUp: Application.ScreenUpdating = True End Sub My files are saved as filenameyymmdd.xls and I need to open and copy in date order but the code is opening newest last. What do I change to get them to open oldest first? Thanks! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change default Open/Files of Type to "Microsoft Excel Files | Excel Discussion (Misc queries) | |||
open files in loop with date order | Excel Discussion (Misc queries) | |||
How to open files in order they were selected | Excel Discussion (Misc queries) | |||
How do you open multiple files in specifc order? | Excel Discussion (Misc queries) | |||
Macro to open *.dat files and save as .txt (comma delimited text files) | Excel Programming |