VBA to use a variable filename

[Teatimedgg]

Good Morning all-

I am looking to run a macro against a downloaded file that will always have a different file name and sheet name every day (example Report123456789). The macro needs to take this file format some columns, change the sheet name to Sheet1 and then save as an excel 97-2003 version to a server drive. The format macro I got, but how can i tell VBA that the file and sheet name will be different each time and then save to a specified location. I have tried report*.xls. Also, i want the macro to be in a separate file and use the activate file (with the Report123456789) when i run it from the separate file.


Any ideas?

thanks in advance!