VBA-OnOpen search for the newest file and append

[MarkCBB]

Hi there,

We have a few daily system generated reports that are dropped into files on the network. these files are labeled "ASD_2011/1/23", "ASD_2010/1/23" etc.

I need a Macro or VBA that runs on open and gets Todays data and then appends it to the current database. however this file may be opened a few times a day, so it needs to be able to workout if it has already collected the data.

Currently I amend the file name every morning and then run this macro.

ChDir "C:\Users\user\Documents\Excel files"
    Workbooks.Open Filename:= _
        "C:\Users\user\Documents\Excel files\ASD_2011/1/23.xlsx"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Windows("MasterStartsReport.xlsx").Activate
    ActiveSheet.Paste
    Windows("ASD_2011/1/23.xlsx").Activate
    ActiveWindow.Close

As you can see this is not the nbst way to do this.

can someone assist?