0

I am trying to find an answer in code for the following problem. I have written the following macro to open around 1000 excel files and extract the required information. The excel files are saved in a folder. The macro opens the files in an alphabetical order. The problem that I have is that I extracted the individual excel files from different zip folders. I would like to let the macro open the excel files not in an alphabetical order, but by the modification date, meaning the order I unzipped the zip folder in the first place. This is required because i need to work with the data in the next steps.

Sub CalcSumms()
    
    ' Leere Zeile in A2 einfügen
    
    Dim Pfad, Dateiname
    Pfad = "D:\Rene\Rohdaten\"
    Dateiname = Dir(Pfad & "*.xlsx")
    
    Application.ScreenUpdating = False
    Do While Dateiname <> ""

        Dim Gemeinde As String
        Dim Arbeitsort As String
        Dim Wohnort As String
            
        Workbooks.Open Pfad & Dateiname
        Sheets("Impressum").Select
        Range("C10").Activate
        Gemeinde = Selection.Value
        
        Sheets("Daten").Select
        Range("D10").Activate
        Arbeitsort = Selection.Value
        
        Sheets("Daten").Select
        Range("D23").Activate
        Wohnort = Selection.Value
        
        ActiveWindow.Close
            
        Range("A2").Cells.Value = Gemeinde
        Range("B2").Cells.Value = Arbeitsort
        Range("C2").Cells.Value = Wohnort
    
        Rows("2:2").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

        Dateiname = Dir()
    Loop
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Florian Wiesemes
  • 1
  • Well then you probably need to loop through your folder first (use the `FileSystemObject` so you can get the dates), collect all the data in an array, sort that array in the order you want to. Finally you can then loop through your ordered array and use that to process your files in order. • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Jun 08 '21 at 09:51
  • See [Loop through ALL files in a folder based on 'Last Modified Date'](https://stackoverflow.com/questions/16288555/loop-through-all-files-in-a-folder-based-on-last-modified-date) – Pᴇʜ Jun 08 '21 at 09:54

0 Answers0