Excel imports files in the wrong order

[xluserg]

Hi All

I have been googling to see if I could solve this but cannot find a solution

My problem is I have this code that loads all xls files from a folder to one worksheet in a workbook. windows 10 File Explorer shows files listed by date order.

C:\\TEMP\results_2013-1-1.xls

C:\\TEMP\results_2013-1-2.xls

C:\\TEMP\results_2013-1-3.xls

C:\\TEMP\results_2013-1-4.xls

C:\\TEMP\results_2013-1-5.xls

C:\\TEMP\results_2013-1-6.xls

C:\\TEMP\results_2013-1-7.xls

C:\\TEMP\results_2013-1-8.xls

C:\\TEMP\results_2013-1-9.xls

C:\\TEMP\results_2013-1-10.xls

C:\\TEMP\results_2013-1-11.xls

C:\\TEMP\results_2013-1-12.xls

C:\\TEMP\results_2013-1-13.xls

C:\\TEMP\results_2013-1-14.xls

C:\\TEMP\results_2013-1-15.xls

C:\\TEMP\results_2013-1-16.xls

C:\\TEMP\results_2013-1-17.xls

C:\\TEMP\results_2013-1-18.xls

C:\\TEMP\results_2013-1-19.xls

C:\\TEMP\results_2013-1-20.xls

C:\\TEMP\results_2013-1-21.xls

C:\\TEMP\results_2013-1-22.xls

C:\\TEMP\results_2013-1-23.xls

C:\\TEMP\results_2013-1-24.xls

C:\\TEMP\results_2013-1-25.xls

C:\\TEMP\results_2013-1-26.xls

C:\\TEMP\results_2013-1-27.xls

C:\\TEMP\results_2013-1-28.xls

C:\\TEMP\results_2013-1-29.xls

C:\\TEMP\results_2013-1-30.xls

C:\\TEMP\results_2013-1-31.xls

Sub LoopAllExcelFilesInFolder()

'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
'SOURCE: [URL='http://www.TheSpreadsheetGuru.com']www.TheSpreadsheetGuru.com[/URL]

    Sheets("Results").Select

Dim WB As Workbook
Dim MyPath As String
Dim MyFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        MyPath = .SelectedItems(1) & "\"
    End With

'In Case of Cancel
NextCode:
  MyPath = MyPath
  If MyPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
  myExtension = "*.xls*"

'Target Path with Ending Extention
  MyFile = Dir(MyPath & myExtension)

'Loop through each Excel file in folder
  Do While MyFile <> ""
    'Set variable equal to opened workbook
      Set WB = Workbooks.Open(Filename:=MyPath & MyFile)

    'Ensure Workbook has opened before moving on to next line of code
      DoEvents
    Dim w As Workbook
    Set w = ActiveWorkbook

' 'Do other stuff
Cells.Select
    ActiveWindow.Zoom = 75
    Selection.Columns.AutoFit
    ActiveCell.Select

    Application.Goto Reference:="R2C1"
    ActiveCell.Range("A1:AT1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
      DoEvents
    
    Windows("COURSE Results.xlsm").Activate

    Sheets("Results").Select
  
      DoEvents

Range("A" & Cells.Rows.Count).End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
  
    Application.ScreenUpdating = True

    Windows("COURSE Results.xlsm").Activate
    ActiveSheet.Paste

    w.Activate

    Application.DisplayAlerts = False

'do the deletions here

    'Save and Close Workbook
      WB.Close SaveChanges:=False

    'Ensure Workbook has closed before moving on to next line of code
      DoEvents
Application.DisplayAlerts = True
    'Get next file name
      MyFile = Dir

  Loop

'Message Box when tasks are completed
'  MsgBox "Task Complete!"
  
Range("A" & Cells.Rows.Count).End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select

ResetSettings:

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

When I run the code excel, loads file data in this order.

C:\\TEMP\results_2013-1-1.xls

C:\\TEMP\results_2013-1-10.xls

C:\\TEMP\results_2013-1-11.xls

C:\\TEMP\results_2013-1-12.xls

C:\\TEMP\results_2013-1-13.xls

C:\\TEMP\results_2013-1-14.xls

C:\\TEMP\results_2013-1-15.xls

C:\\TEMP\results_2013-1-16.xls

C:\\TEMP\results_2013-1-17.xls

C:\\TEMP\results_2013-1-18.xls

C:\\TEMP\results_2013-1-19.xls

C:\\TEMP\results_2013-1-2.xls

C:\\TEMP\results_2013-1-21.xls

C:\\TEMP\results_2013-1-22.xls

C:\\TEMP\results_2013-1-23.xls

C:\\TEMP\results_2013-1-24.xls

C:\\TEMP\results_2013-1-25.xls

C:\\TEMP\results_2013-1-26.xls

C:\\TEMP\results_2013-1-27.xls

C:\\TEMP\results_2013-1-28.xls

C:\\TEMP\results_2013-1-29.xls

C:\\TEMP\results_2013-1-3.xls

C:\\TEMP\results_2013-1-30.xls

C:\\TEMP\results_2013-1-31.xls

C:\\TEMP\results_2013-1-4.xls

C:\\TEMP\results_2013-1-5.xls

C:\\TEMP\results_2013-1-6.xls

C:\\TEMP\results_2013-1-7.xls

C:\\TEMP\results_2013-1-8.xls

C:\\TEMP\results_2013-1-9.xls

Link to the code I’m using

Loop Through All Excel Files In A Given Folder — The Spreadsheet Guru



I'd be much appreciated if anyone has a solution

Thanks in advance

Graham

 

[Joe4]

Excel actually is importing them in the correct order. Note that files are text entries, so they are being sorted according to text rules (alphabetically).
So the order of the files you listed at the end of your post is actually in alphabetic order (i.e. results_2013-1-19.xls comes before results_2013-1-2.xls alphabetically).

The easiest way to get them to sort and order the way you want is to change the date format you are using in the file naming.
Instead of sometimes having one character for month, sometimes two and sometimes one character for day, and sometimes two, always use two characters for both (i.e. "yyyy-mm-dd").
Then you will have names like:
results_2013-01-19.xls
and things will sort and import in the order that you want.

 

[xluserg]

Hi Joe4

Thanks for your Reply

I understand what you mean I was hoping to do this in one go but so be it.

It would be time consuming to manually change all the dates as I have a good few years to do. Maybe I could use a file renamer but that could cause problems.

The way I was do it was copying all files up to the 9th into the folder then load them to excel, when finished delete them, then do the 10th to the end of the month.

I will ponder over this.

Thanks very much for your help.

All the best

Graham

 

[Joe4]

Graham,

You can create a little procedure to rename all of your files at once, like this:

Sub RenameFiles()

    Dim myDir As String
    Dim fname1 As String
    Dim fname2 As String
    Dim dte As String
    Dim chg As Boolean
   
'   Directory to run on
    myDir = "C:\Temp\"
   
'   Add last backslash if missing
    If Right(myDir, 1) <> "\" Then myDir = myDir & "\"
   
'   Loop through all files
    fname1 = Dir(myDir & "*.xls")
    Do While fname1 <> ""
        chg = False
'       Get date portion of file name
        dte = Mid(fname1, 9)
        dte = Left(dte, Len(dte) - 4)
'       Insert missing zeroes in month portion of date
        If Mid(dte, 7, 1) = "-" Then
            dte = Left(dte, 5) & "0" & Mid(dte, 6)
            chg = True
        End If
'       Insert missing zeroes in day portion of date
        If Len(dte) = 9 Then
            dte = Left(dte, 8) & "0" & Right(dte, 1)
            chg = True
        End If
'       If any changes made, rename file
        If chg Then
'           Build new file name
            fname2 = "results_" & dte & ".xls"
'           Rename file
            Name myDir & fname1 As myDir & fname2
        End If
'       Move to next file name
        fname1 = Dir
    Loop
   
    MsgBox "File renaming complete!"
   
End Sub

This should work as long as the files following the naming convention you have shown in your examples.
If you need to change the directory, just change this line here to match your file directory:
myDir = "C:\Temp\"

 

[offthelip]

An alternative way of doing this is to do it in two stages , stage one create a list of all the files in a excel spreadsheet with the full path name and the last modified date or the created date which ever you want.
The sort the list in date order, then import the files from this list.
To get the full path and modified date use the File system object and date modified, here is some code that shows you how to generate a list of files and paths with the date modifed, you could add the sort and import using vba automatically :

Sub test()
' add reference to Microsoft scripting runtime
Dim FSO As New FileSystemObject
Dim myFolder As Folder
    Dim mySubFolder As Folder
    Dim myFile As file
    Dim txtp As String
    tt = ActiveWorkbook.Path
    
Set myFolder = FSO.GetFolder(tt)

 indi = 1
    
 For Each myFile In myFolder.Files
 
  Cells(indi, 1) = myFile.Path
  Cells(indi, 2) = myFile.DateLastModified
  indi = indi + 1
 Next
 
    End Sub

 

[xluserg]

Thanks for the code Joe4 worked a treat.

offthelip will try your code too. Thanks

 

[Joe4]

You are welcome.
Glad we were able to help!