Developing a Connection to a Varying File Name (VBA)

[Rerun]

Hey all. Thanks in advance.

Here is my issue: I am working with an Excel file that will need to create a data connection to another file and import it into a tab titled "Master Staff". The file location will always be the same, but the file name will change, based on date. It will have the following naming format: "* MM-DD-YYYY HH-MM-SSSS".

I am really having trouble figuring out the VBA coding for this. The date/time will always change and I want to pull the file with the name that is closest to Now().

Any thoughts?

[TMS]

You could have a control sheet with the location (folder) where the files exist. You could then have a routine to loop through that folder and record the file names in the control sheet. Once you have a list, you could then do a VLOOKUP or INDEX/MATCH to find the nearest matching file name. Once you have that, open the file.

I suspect that, rather than storing the file names in the control sheet, you could use an array but I doubt it would be that much faster.

Regards, TMS

[Rerun]

My issue is that ultimately I need to pull data from a workbook that has a filename with a date that is the most recent. So, I need to import that data to complete my table.

[TMS]

I've explained how you could go about locating and matching the file. What you do with it once you have found it and opened it is up to you.

Regards

[Rerun]

Can anybody offer advice involving a VBA formula where I can open the most recent file without it being a manual process? My preference is that the file is not opened at all and instead I can just import the necessary data based on an array and for-next loop I will create. The difficult part has been actually opening up what is needed.