2

I am using VBA, and looping through spreadsheets in a specific folder - opening the spreadsheets, coping some lines then pasting it into a master spreadsheet. I made use of the following link to achieve the loop:

Loop through files in a folder using VBA?

My problem/question: the spreadsheets have the date saved as the file name - for example:

Trader Estimate T+1 Consol_20131003.xlsx

But it seems using the Dir function, that the spreadsheets are not being opened in the chronological order as dictated by the date in the filename.

How would I achieve this opening in chronological order (according to the filename)?

Don't know if this makes a difference but the spreadsheets are not saved on this computer in chronological order - ie. if I order the folder of spreadsheets by date modified, they are not in perfect order as dictated by the date in their filename.

Any help is very much appreciated

Community
  • 1
  • 1
  • 1
    Should be migrated to StackOverflow (I can't choose this from the off topic migration menu, only meta is available there). – Bob Jansen Dec 09 '13 at 13:35
  • Sorry Bob, could you explain why it should be migrated to stack overflow so I do not make the same mistake in the future –  Dec 09 '13 at 13:38
  • This site is for professionals involved with quantitative finance. Your question is more akin to a basic programming problem which is what StackOverflow can help you with. – Louis Marascio Dec 09 '13 at 13:43
  • Dir returns files in directory order, not sorted alphabetically. You could instead read the filenames into an array and then sort the array (see reference to QuickSort below) – Steve Rindsberg Dec 09 '13 at 14:02

1 Answers1

1

My be you will find these links helpful to get started

QuickSort(Get a sorted list of files in a directory using Dir and Quicksort)

Similar Question here

Community
  • 1
  • 1
Youbaraj Sharma
  • 1,295
  • 3
  • 17
  • 34