check if file exists and opening it based on file date

Hi guys,

I'm trying to create some code that opens a file from a network location, based on filename / date.

First, I have userform input from the user that determines a weeknumber, year and company that he or she wants to see the analysis from - this constructs the network path where the files are :

so, the path will be something like this :
\\networklocation\information\2014\CompanyX\Week_12\Analyse
where the bold parts are gathered through userform input

What I'd like to have is a code that

• checks if that network location is there
• checks if one or multile files containing the word "doorverkoop" are in that location
• opens the file with the word "doorverkoop" in it that has the most recent file date
• if the file or network location are not found, have a popup allow the user to open a file he or she wants to import

Since I'm not great with file handling in VBA, I'd appreciate it if anyone can help me out / point me in the right direction.
Thanks so much in advance!!

Maybe this can help you some. I believe a more experienced person might have another suggestions. But until someone else answer, this can help you further.

Following code looks for all files containing "*.xls" in path "C:\Temp". Change to your path and to "*doorverkoop*" in your code

Due to I don't know your filename containing date, I have left that part. I open the last file in my array.

Please Login or Register  to view this content.

Hi Joakim,

thanks for your reply - but actually proofing which of the files has the latest file date is the key part I want to do
Anyone can help on that, please?

Is the date included in the filename or is it when it was saved? If the date is a part of the filename, please provide an example.

Originally Posted by JasperD

Hi Joakim,

thanks for your reply - but actually proofing which of the files has the latest file date is the key part I want to do
Anyone can help on that, please?

I think apo's solution is great.

I just want to show it is possible to use Dir() with function FileDateTime() also. Notice that the code beneath is checking for *.xls* in path C:\Temp\.

Please Login or Register  to view this content.