Macro to open an excel file based on the latest date found in filename

[salasv]

Hoping I can get assistance with something I've seen lots of various solutions for, but none of them meet my needs.

I'm on excel 2010 and I have a small group excel files I open everyday. Most of the files are static in name and location. I've got a macro created to open those files, which works fine with workbooks.open and the file path.

There are two report files I want to incorporate into my macro of workbooks to open. The files are created weekly and the files names have the following format: "Report Name (YYYY-MM-DD).xlsm". I don't want to use the file's last modified date because older files may get edited after the more recent ones are created. The files are also not always created on the same day, so the solution needs to be flexible enough to not refer to a specific day of the week or anything.

I found this previous thread with the same problem, but since it was a couple years old, I thought I'd better start a new thread.
http://www.mrexcel.com/forum/excel-...el-file-based-latest-date-found-filename.html

The solution in this thread was to provide a starting date to work backwards from, which I don't want to do. The option of counting backwards from today would be good, but I'm not well-versed enough in VBA to make this work on my own. I may also be opening the file the same day it was created, if this matters.

Thanks in advance for your assistance!

Cheers!

Sal

 

[Jerry Sullivan]

Hi Sal and Welcome to the Board,

Here's some code you can try...

Sub OpenLatest()
'---Opens a sheet based on date, searches backward from today til it finds a matching date

    Dim dtTestDate As Date
    Dim sStartWB As String
    
    Const sPath As String = "C:\TEST\"
    Const dtEarliest = #1/1/2010#  '--to stop loop if file not found by earliest valid date.

    dtTestDate = Date
    sStartWB = ActiveWorkbook.Name
    
    While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
        On Error Resume Next
        Workbooks.Open sPath & "Report Name " & Format(dtTestDate, "(YYYY-MM-DD)") & ".xlsm"
        dtTestDate = dtTestDate - 1
        On Error GoTo 0
    Wend
    
    If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."
End Sub

You can remove the parenthesis from "(YYYY-MM-DD)" if those are not literally in your filename.

 

[salasv]

Thank you Jerry!! This worked perfectly! I really appreciate the quick response. Have a great day!

 

[bprisk]

Jerry (or any), how can this code be modified to a) open the file in sPath with the most recent date, and b) open it from a network path?

My files are stored on a SharePoint server, and the file I would be looking for may have been created today, or three days ago. It looks like I should easily be able to modify the date I'm looking for (it's part of the filename) with the dtTestDate variable. I believe that my biggest headache is to get the macro to keep looking in the network location, rather than a "normal" path.

And to muddy the waters, the filename date does NOT pad with a leading zero on the day (4SEP13 vs 04SEP13). I'll have to use a string function to pull the 6 or 7 characters from the filename depending on length, eh?

 

[salasv]

I can tell you what I did to modify this for my purposes, hopefully it helps with your question. If you already have a file on SharePoint to use as a starting point, right click it and copy the link to the file. You can then paste the link to the sPath and cut the file name to leave just the directory information. sPath just gives the directory to look in whether it's a network drive or SharePoint. You could probably just copy the URL from your browser, but I prefer this method to be sure I capture everything correctly.

For the file name, you can paste it from the URL in the step above and, I think, you would just change the date format to dMMMyy. Of course, remove the directory in this step & make note of the quotes so you can put them in the right place in your file name.

On a side note, I also changed the date in the following piece of code to 2013 because I knew I wasn't going further back than this year.

Const dtEarliest = #1/1/2010#

This code was very helpful to me and I use it open several reports I reference every day, which happen to a mix of locally saved and SharePoint saved files. I hope it helps you as well.

 

[bprisk]

Agreed with your solution on obtaining the path. I've gotten most of the way to where I need to be after my post, by continueing to disect the code. The part that has me stumped is that the While loop keeps executing until it finds the OLDEST file. I need the routine to generate the error (and resume next) if it cannot find a file with today's date, then decriment the date one day and look again, etc., until it finds the most recent file, then exit the loop (after opening the file). The While loop written in the example above code keeps opening files until it finds the oldest file. (Luckily, I only have two files to experiment with!!)

 

[bprisk]

I misspeak. The code above is doing exactly what I want it to do on my macro. Here's the code I have right now:

.
.
.
dtTestDate = Date
On Error GoTo FindRecent
Workbooks.Open sPath & "File_Name_" & Format(dtTestDate, "ddMMMyy") & ".xls"
Exit Sub

FindRecent:​

sStartWB = ActiveWorkbook.Name
dtTestDate = dtTestDate - 1
While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest​

On Error Resume Next
Workbooks.Open sPath & "File_Name_" & Format(dtTestDate, "ddMMMyy") & ".xls"
dtTestDate = dtTestDate - 1
On Error GoTo 0​

Wend
​

.
.
.

The first section works correctly. If I have a file in the folder with today's date it opens it and exits the Sub. But if the only file in the folder is dated 09SEP13, the program generates an Error 1004 window when it can't find a file with the date of 11SEP13. Why isn't the error handling working?

 

[Jerry Sullivan]

Hi all,

Sal, I'm glad to hear that you're making use of the code and have been able to modify it to work on a Sharepoint site. Please see my note at the end of this post regarding alternative approaches.

bprisk, I'll try to address your last question and a few points made earlier.

The first section works correctly. If I have a file in the folder with today's date it opens it and exits the Sub. But if the only file in the folder is dated 09SEP13, the program generates an Error 1004 window when it can't find a file with the date of 11SEP13. Why isn't the error handling working?

That's because in VBA you can't handle an error that occurs within an error handler. When your code execution jumps to FindRecent:, VBA considers that you are "in an error handler". You would need to exit the error handler, (for example with a Resume statement), prior to being able to set a different error hander. You could Resume "forward" to another line number or label, but I don't believe that's necessary for what you want to do.

And to muddy the waters, the filename date does NOT pad with a leading zero on the day (4SEP13 vs 04SEP13). I'll have to use a string function to pull the 6 or 7 characters from the filename depending on length, eh?

If your stored files are consistent in not having a leading zero on the day, then this should work....

Format(dtTestDate, "dMMMyy") & ".xls"

The part that has me stumped is that the While loop keeps executing until it finds the OLDEST file. I need the routine to generate the error (and resume next) if it cannot find a file with today's date, then decriment the date one day and look again, etc., until it finds the most recent file, then exit the loop (after opening the file). The While loop written in the example above code keeps opening files until it finds the oldest file. (Luckily, I only have two files to experiment with!!)

This puzzles me. The original code shouldn't open more than one file because once the first (Newest) file is opened it should become the ActiveWorkbook and the loop will stop when this expression evaluates to False

While ActiveWorkbook.Name = sStartWB

It's possible that there's a syncing issue in which the code is continuing its execution while the SharePoint connection is downloading a file, but I've tested the modified version below on a slow remote connection and it correctly waited for a response from the Open statement before continuing execution.

Here's a modified version you could try after modifying the Path and Filenames....

Sub OpenLatest()
'---Opens a sheet based on date, searches backward from today til it finds a matching date

    Dim dtTestDate As Date
    Dim sStartWB As String
    
    Const sPath As String = "https://mycompany.sharepoint.com/My%20Test%20Reports/"
    Const dtEarliest = #8/5/2013#    '--to stop loop if file not found by earliest valid date

    dtTestDate = Date
    sStartWB = ActiveWorkbook.Name

    '--add this to suppress error "The Internet address... is not valid"
    Application.DisplayAlerts = False

    While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
        On Error Resume Next
        Debug.Print "Trying to open: " & _
            sPath & "File_Name_" & Format(dtTestDate, "dMMMyy") & ".xls"
        Workbooks.Open sPath & "File_Name_" & Format(dtTestDate, "dMMMyy") & ".xls"
        dtTestDate = dtTestDate - 1
        On Error GoTo 0
    Wend
    
    Application.DisplayAlerts = True

    If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."
End Sub

So all that being said....I don't think this is a good approach for use on a Sharepoint Site.

The earlier code was in response to Sal's question about how to modify Jerry Beaucaire's code to get it to count backwards from today instead of a fixed date. I tweaked it a little by adding dtEarliest because it was clearly inefficient to count all the way to the year 1900 if the file wasn't found. The approach was still more brute-force than ideal, but it worked pretty fast on a local PC or Server.

For a slower network scenario like accessing a Sharepoint site via a remote Internet connection it would be significantly more efficient to read a file list in the target folder, find the latest file matching the criteria and then Open that file (one Workbook.Open call).

If someone can suggest or track down some code that reads a target folder's filenames on a Sharepoint site, I'd be glad to help modify the code in this thread to work faster.

 

[bprisk]

Jerry,

Thanks for detailed response. Very educational.

I agree that it would be better to read the contents of the SharePoint folder and open the file based on the list. I'm still getting the hang of working with SharePoint at this level.

As for opening multiple files, the macro (at this point) is embedded in the "target" workbook - the one that will eventually hold all the data I'm extracting from the file(s) I'm opening from the SP site. Perhaps the macro forces that one to be the active workbook every time it executes the Open command? I end up with multiple files open by the time it stops on the correct file.

I believe that I'll head down the road of reading a list of the target SP directory. I'll keep flogging it while I wait for more feedback here.

A question regarding the date format. Would the format "dMMMyy" truncate the leading digit for dates higher than 9?? That would be bad. I'll experiment with it...

 

[Jerry Sullivan]

As for opening multiple files, the macro (at this point) is embedded in the "target" workbook - the one that will eventually hold all the data I'm extracting from the file(s) I'm opening from the SP site. Perhaps the macro forces that one to be the active workbook every time it executes the Open command? I end up with multiple files open by the time it stops on the correct file.

I'm not sure why that's happening. It shouldn't behave that way, and it doesn't do that on my test workbooks.
If you go with the approach of reading the file list and only opening one file, that mysterious issue will be moot.

A question regarding the date format. Would the format "dMMMyy" truncate the leading digit for dates higher than 9?? That would be bad. I'll experiment with it...

It will work like you want. You can do a quick test in the Immediate Window of the VB Editor.

?Format("01/01/2013","dMMMyy")
1Jan13 'returned value

?Format("12/12/2013","dMMMyy")
12Dec13 'returned value