Open most recent external file

[starcraftbud]

Hey guys,

CSV files are systematically dumped into a folder. I'm looking to open the most recent one. I cannot delete or modify existing files in the folder.

Files always follow this naming format: Something_0419_Data.CSV

The problem I am facing is that the date - where it says "0419" is not always today's date. Files could have been added prior day or several days prior. So, I'd really need to go off date modified. I also need to go off date modified because prior year files are also in this folder. So it is possible for "Something_0419_Data.CSV" to be 2010 and the most recent file, but there could exist a file called "Something_0420_data.CSV"

I've searched the boards and looks like I need to use an FSO property? I have been trying several different codes I've gathered from threads but I can't get it quite right. I'm still more in the novice arena of VBA. Could someone please assist me with this issue? Thank you very much for the help.

[Leith Ross]

Hello starcraftbud,

Here is a macro to find and open the most recent CSV file in a folder. You will need to change the folder location for your system.

Please Login or Register  to view this content.

[starcraftbud]

Hey Leith,

I'm very glad you responded. I think I was trying a hybrid of this kind of code and was having trouble. There is a slight wrench thrown in that messes me up. The file I need is mixed in with other files to create a scenario where the file I need is not always the last modified file. To be more specific, I always need the last modified file following this naming convention "Something_0419_Data.CSV". Yet, there might be more recently modified files under different names like "SomethingNew_0419_DataFile.CSV". So, the files follow different naming formats. Would it be possible to build into the code a functionality to only search for naming convention of "Something_0419_Data.CSV"?

I couldn't quite figure out how to modify your provided code to fit this issue. There is still a lot I need to learn. I am able to follow the code pretty well, I'm just not quite sure how to modify it without causing problems. I'm sort of new to using "objects". Thanks again for the help.

[shg]

Maybe like this:

Please Login or Register  to view this content.

Note the reference required.

[starcraftbud]

Hello SHG,

Thank you for the response. I am attempting to make this work. One question I have to ask(sorry). How can I account for the numbers in the filename?

I changed the constant, but VBA doesn't like it. I'm still working through it and using google and these forums for a solution, but I thought I'd post the question in case you or someone can solve it quickly.

I'm trying to change this:

Please Login or Register  to view this content.

Doesn't this need to be a closer replication to the exact data file name (e.g. "Something_0419_Data.CSV")?

Please Login or Register  to view this content.

I am trying to open the file which is the most recent but follows this filename format "Something_0419_Data.CSV" - with the possibility of the 4 numeric characters changing but they are always 4 integers.

Thanks again SHG, I'm very close I think.

[shg]

Look at VBE Help for the Like operator and the wildcards it supports, and post back if you still need help.

How tight the match string needs to be depends on the range of file names within which the files of interest fall. It could range from "*.csv" to "someveryspecificnamewithjustthis#variation.csv"

[starcraftbud]

I understand better now. The "*" is not a mistake. It signifies zero or more characters.

So the code you provided was already taking my question into account. You were one step ahead of me. I now understand that, but I'm getting a compile error in the function aspect of the code.

The error states "User-defined type not defined"

And the line of code is

Please Login or Register  to view this content.

Well, the entire Function line is yellowed out, but the code above is highlighted.

That you for the quick response earlier and I'm very glad you are being patient and helping me out. I'm sorry this is not working out so easily. I'm also certain that sPath and sLike are correct.

[shg]

See the last line of post #4, and the first comment in the function.

[starcraftbud]

Yippee!

That was the problem. It now works like a charm. I will mark this as solved.

Thanks for the help. You should now play the song "Simply the Best" by Tina Turner. I think its by Tina Turner. You know the song. That would reflect how great a job you did in helping me out.

[shg]

You're welcome

Simply the best! Better than all the rest!