Issue with opening multiple files via a file name list via VBA

[ewenger]

Hi guys! Hope you are all well!

This is the first time I try this forum, so please do let me know if I didn't do something correctly. Also, I am a self taught VBA noob, so it would be greatly appreciated if you can please dumb it down as much as possible in the explanation/code. Thanks heaps for your help in advance! So here we go....

Each day, the system will create 3 files in the morning, afternoon and night into the folder. The file will start with the same file name with today's date but just end with different suffix. To make the matter simpler, let's assume the suffix here is randomly generated. Then the system will generate these files through out the month.

e.g.
File_02_May_3214
File_02_May_5425
File_02_May_6534
File_03_May_5424
File_03_May_3224
File_03_May_1343
File_04_May_7654
File_04_May_4324
File_04_May_9876

At the month end (after daily files for the entire month are generated), my goal here is to open each day's file which has the latest modified date in the folder and copy something from each of the file and paste on to a worksheet.

So for example, for the below files

e.g. (files in the folder)
File_02_May_3214 (<=modified date 9:00am)
File_02_May_5425 (<=modified date 13:00pm)
File_02_May_6534 (<=modified date 5:00pm)
File_03_May_5424 (<=modified date 9:00am)
File_03_May_3224 (<=modified date 13:00pm)
File_03_May_1343 (<=modified date 5:00pm)
File_04_May_7654 (<=modified date 9:00am)
File_04_May_4324 (<=modified date 13:00pm)
File_04_May_9876(<=modified date 5:00pm)

I will want to open File_02_May_6534, then copy some data and paste them into a worksheet, then move onto File_03_May_1343 and File_04_May_9876 to do the same.

My solution to do the above action via VBA is to firstly create a daily file name list with wildcard naming convention in Excel (like the below).

e.g. (file name list)
File_02_May_*
File_03_May_*
File_04_May_*

I will loop through this daily file name list and in each loop, I will also find the daily file with the latest modified date via another loop and once found, i will open and copy some data out of those files. However, I have some issue doing it via my below code (red text section) unfortunately.

As you can see, there are two loops with the below code. One is to "loop through the daily file name list" and another is to "loop to find the daily file with the latest modified date in the folder". Once the file is located, it will open it up and copy/paste some data out of it. However, for some reason, once it finds the latest file for the day, instead of going to open the file, it goes to find the next latest modified file for the next file on the daily file name list.

So using the above example again, once my code finds File_02_May_6534, instead of opening it, it goes onto searching for the next daily file (File_03_May_*) with the latest modified date in the loop. Can someone please help to provide some insight for this issue here? Or is there a better or simpler way to tackle this issue? Thank you so much!

Sub Import_file()

Dim myDir As String
Dim File As Range
Dim FileName_widecarded As String
Dim msg As String
Dim LMD As Date
Dim Latestdate As Date
Dim Latestfile As String

 myDir = Range("File_directory")
 
 For Each File In Range("File_range")
 
 FileName_widecarded = Dir(myDir & File, vbNormal)
   
    If FileName_widecarded <> "" Then
   
        Do While Len(FileName_widecarded) > 0
       
        LMD = FileDateTime(myDir & FileName_widecarded)
    
        If LMD > Latestdate Then
        Latestfile = FileName_widecarded
        Latestdate = LMD
       
        End If
       
        FileName_widecarded = Dir
       
        Loop
      
        Workbooks.Open myDir & Latestfile
        ActiveSheet.Range("A1:J5000").Copy Destination:=ThisWorkbook.Worksheets("Data raw").Range("A1:B20")
        ActiveWorkbook.Close False
      
    Else
  
    msg = msg & vbLf & File.Value
  
    End If
  
 Next

 

[shknbk2]

Welcome to the forum. What happens when you step through the code? Put the cursor anywhere in the code and start hitting F8 to step through each line. You'll see a yellow line going though the code after each F8 press. You can put the mouse cursor over a variable (LMD, for example) and a little popup display will show its current value. Does the code arrive at the Open line or does it skip it somehow?

 

[ewenger]

Hi shknbk2!

Thanks for your quick response here. Apologies for not being able to reply a bit sooner.

Yes, I did a F8 to step through the process previously. Here is what I found.

In between the Do while and Loop below, the macro successfully found the latest file with the same file name suffix (e.g. File_02_May_*) in the folder. So using the example above, it found File_02_May_6534 as Latestfile during the loop. However, instead of opening the file (since there is no more file with File_02_May_ suffix in the folder), it went on finding the next latest file based on the file name list created in the worksheet and in the next loop it started finding the file with File_03_May suffix. This is what puzzled me the most here,I as soon as the latest file was found, I would expect the macro to open it and then go on to the next "For each" loop and take the next file with different suffix, not go straight back to the Do while loop....?

Thanks again for your help!! Really appreciated!

Do While Len(FileName_widecarded) > 0

LMD = FileDateTime(myDir & FileName_widecarded)

If LMD > Latestdate Then
Latestfile = FileName_widecarded
Latestdate = LMD

End If

FileName_widecarded = Dir

Loop

 

[shknbk2]

Your code works for me when I use test files. After it finds File_02_May_6534 and goes through the If statement to set Latestfile and Latestdate, FileName_widecarded gets a blank return from the Dir (as you would expect) and when it goes back to the Do While line, it fails and exits the Do loop like it should.

After your code updates Latestfile and Latestdate when 6534 is done processing, stop the yellow line after FileName_widecarded = Dir gets executed. It seems like your code is returning the 03_May files at this point, right? Where mine is returning an empty string since there are no more 02_Mays.

 

[ewenger]

Your code works for me when I use test files. After it finds File_02_May_6534 and goes through the If statement to set Latestfile and Latestdate, FileName_widecarded gets a blank return from the Dir (as you would expect) and when it goes back to the Do While line, it fails and exits the Do loop like it should.

After your code updates Latestfile and Latestdate when 6534 is done processing, stop the yellow line after FileName_widecarded = Dir gets executed. It seems like your code is returning the 03_May files at this point, right? Where mine is returning an empty string since there are no more 02_Mays.

Hi shknbk2,

Thanks heaps for another quick response. I think I found where the problem is now. The actual problem is with the Latestfile & Latestdate after the first successful found through the loop. For example, once the Latest File_02_May_6534 is found to be the latest file for the given suffix (File_02_May), it remain to be the Latestfile & Latestdate for the remaining loops. This wouldn't be a problem if any next file (e.g. with File_03_May suffix) on the list is created after the File_02_May_6534, but if it does, the loop will compare assess the latest file and latest date against Latestfile & Latestdate obtained from the File_02_May_6534.

Now....this will normally not be a problem, because (you probably already notice) the suffix coincides with the date the file is generated. So when the loop goes to the next file suffix, it generally means it will go to the next (later) date, hence the Latestfile & Latestdate will be superseded without any issue. However, it's just so happen that I have a file with earlier date suffix but later modified date. This the become the "wrong" reference point for the rest of the entire loop.

I think my question is then, because I can't be certain if this file behavior (earlier date with later modified date), how can I make sure this won't happen and Macro will handle the situation as what I intend it to be?

Thanks heaps again for your help in advance!!

 

[shknbk2]

I haven't used FileDateTime much in the past, so I read up on it. I'm not sure how it determines which parameter to return, but the literature says that it returns the creation or modification time. I would guess it returns the latest time between the two (creation being returned only if the file has never been modified).

So, maybe the trick is to guarantee access to the creation time. This assumes, however, the when it gets "created" by the server, it is actually being created as new and not copied from a master file and modified. If copied, it would probably copy the creation time and have a new modification time. If this is the case, I don't know how you would determine the latest file based on your explanation of the modification times being not in order.

If the creation time is in proper order, the rest of this reply might work.

The Microsoft VBA reference gives the following example related to a File object, which has access to much more specific parameters of files.

Sub ShowFileInfo(filespec)
    Dim fs, f, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(filespec)
    s = f.DateCreated
    MsgBox s
End Sub

In your case, s would be the variable you would replace with LMD. You could either incorporate these example lines into your current procedure or turn it into a Function to return the value:

Function GetFileInfo(filespec) As Date
    Dim fs, f, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(filespec)
    s = f.DateCreated
    GetFileInfo = s
End Function

With this function, you can replace your "LMD =" line with:

LMD = GetFileInfo(myDir & FileName_widecarded)

It seems like this would work because no matter when the file is later modified, its creation time should always be the same; again, assuming the creation times are in order.

 

[ewenger]

Thanks again, shknbk2!

Ah~that's a very clever alternative solution! I will take a look at that and see the system behaves and generate the file in a way that I can benefit from using the code. Thank you so much for your help!