VBA loop through non-patterned files in folder (seek for opinions) [EDIT]

Question

I am here for seeking any advice or opinion as I want to loop through every excel files in folder. As you can see from my attached picture, my excel files are different both in file types (.xls <> .xlsx) and filename (especially on 2018). I also need to loop through "Revised" or "revised" files as well since it is possibly that any file will be revised next time.

And yes, I also did some research on this. My understanding is I need to modify all of the file names into the pattern one before build up a VBA to loop. At first, I thought about decomposing all filenames and put it back in pattern form, but it sound too idealistic. Another way is using the date in each file to label the workbook name, but again I found that those date had different styles. Some files label the date by using string such as "January 2012" or "March 2014", while the others using the date form such as "19/08/2013".

Therefore, I would be appreciated if anyone could suggest me on;

1. How can I handle with the different file name (.xls and .xlsx) within the same VBA?
2. How should I deal with these different file names (some files have "revised" at the back; some do not have "-" between "Cons" and date; and some use month name instead of number)? Are they any pattern that I overlook?

Please noted that I am just a newbie VBA coder, so it would be great if you left your answers with an explanation or any kind of examples.

Many thanks.

--------------------------------------------------------[EDIT]-------------------------------------------------------------------

First of all sorry for my poor explanation before. I provided too few information to understand overall picture. Let's start this over again.

1. My data are about steel consumption which release from the authority every month. My task is to gather all of these data (such as production, import, export and consumption of every data in each row) and generate into time series pattern (please see attached excel screen)

2. As it is possibly that these data will be revised anytime, I thus decide to download all of these file every time in every month (one file per one month). In addition to those revised file, the authority will unexpectedly rename those file for example, from "Cons 201601.xlsx" into "Cons 201601 - revised.xlsx)". This make me more difficult to work on this (please see attached folder for reference).

3. Moreover, this authority seems to have a problem with file naming as they had different pattern of filename in the past compare to the present ones. Example is per below table;

   Cons 201701-Revised.xlsx 
   Cons 201710-Revised1.xlsx 
   Cons 201711.xlsx
   Cons-200902.xls 
   Cons-201212_revise.xls 
   Cons-201401-revised.xls

4. I mention above file name in order to create a VBA to loop through these file, select some content and paste into another workbook in chronological order. This means that I cannot use "Loop while or Do while function" in my VBA. At first I decided to use two integer variables, both of which were set for years and months (e.g. For i = 2009 to 2018 and For j = 1 to 12) in order to created the system of filename (such as filename = "Cons" & "-" & i & j). But, as I stated before, non-patterned name by the authority had prevented me from creating this kind of loop.

5. I also tried to use the date in cell B2 in figure 1 to label the date in order to create the loop which I already explained before. However, again, the authority did not use the same pattern to date month and year. After I checked with many file, these are example of the date style in cell B2

   January 2012 (string) 
   February 2009 (string) 
   Jan-16 (1/1/2016 date in custom format) 

6. Given above limitations, could you guys again suggest me any possible way to create chronological loop so as to copy and paste data to another workbook to form a set of time series data for each product?

Thank you for your kind help :)

Answer 1

Firstly, use FileSystemObject (include a reference to Microsoft Scripting Runtime in your VBA project) which has some helpful functions within it. You could always code your own, but why reinvent the wheel in this case?

Don't have time to codes something this morning, so here is the pseudocode:

Open a Folder using your known filepath
Loop through all the files in the Folder (For each f in Folder.Files
    extract the date code from your filename (e.g. using RegEx)
    Add to a collection (e.g. array or Dictionary item) of the filename and the extracted date code (your key).
(end loop)
Sort your collection based on the extracted date code

This now gives you an ordered list of files, which you can open in turn and extract the data. An added bonus is that the key in the collection gives you a consistent date representation which you can use as an index in your collated information.

Answer 2

If you just want to loop through all files in folder use this:

   dim file as variant
       file = Dir("<PathToFolder>")

        While (file <> "")
        'Your logic here
        file = Dir
        wend