1

I am using the Dir() function in excel vba to sort through files in a folder and perform some transformations on each file. Dir() is going through the files in alphabetical order by file name by default. I want to be able to specify what order the Dir() function goes through the files. Specifically, I want it to go through the files in order by date modified.

The macro goes through a user specified folder, opens .txt files from the folder in excel, formats the data, calculates statistics for the data (average, st dev, etc), and then copies those statistics into another excel file. I have found this question which sorts the files alphabetically, but I need to sort them by date modified. I've copied the relevant portion of the code below. I could not figure out how to get the syntax highlighting to work so I apologize in advance if it is difficult to follow.

Sub MahloDataToText()

     Dim folder_to_search As String
     Dim full_path As String
     Dim the_file_name As String

     folder_to_search = ActiveSheet.Cells(4, "D").Value
     Const pattern_recognition As String = "*.txt"
     the_file_name = Dir(folder_to_search & pattern_recognition, vbNormal)

     Do While Len(the_file_name) > 0

          full_path = folder_to_search & the_file_name

          Workbooks.OpenText Filename:=full_path, Origin:=437, StartRow:=5, DataType:=xlDelimited, TextQualifier:= _
                        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, _
                        Comma:=False, Space:=False, Other:=False

          'here is where all the formatting, statistics, and copying take place. Excluded for space'

          the_file_name = Dir
     Loop

End Sub

Basically the problem is the order in which the Dir() function is opening the files. It is opening them in alphabetical order and I need them opened in order by date modified. I have tried manually sorting the folder the way I want ahead of time, but that does not seem to impact the Dir() function.

If there is a way for me to specify which order the files are opened then that will solve the problem.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
JBack
  • 11
  • 2
  • My first thought is - can you have the filenames varied by when they were saved? For example if there was a filenaming convention such as "yyyy-mm-dd" and then a timestamp, then it would be possible for the VBA to select the right file first from the directory with the right critieria – Plato77 Sep 12 '19 at 15:22
  • 1
    Looks like [there's no guarantee of the sort order](https://stackoverflow.com/a/4283011/4717755) for the `Dir` function. – PeterT Sep 12 '19 at 15:25
  • 3
    Look through all the files, putting the filenames and modification date `FileDateTime()`, then sort the array and work from there – cybernetic.nomad Sep 12 '19 at 15:25
  • The code posted here (https://codereview.stackexchange.com/questions/140179/wrapper-class-for-the-shell-dir-utility) likely includes a lot of functionality you don't need, but seems relevant. – chillin Sep 12 '19 at 16:27
  • Also, not to derail existing discussion, but seems like you're opening semi-colon delimited text files, transforming them (in some way), computing some statistics, then copying the computations to some other workbook. Have you tried Power Query for this? `Folder.Files` will let you view files in a folder, `Table.Sort` will let you sort the files by date modified. Delimited files can be opened/parsed. Standard library includes some computations (`List.StandardDeviation`, `List.Average`, etc). Custom functions can be made for custom operations. The output would be a single Excel table. – chillin Sep 12 '19 at 16:28

0 Answers0