Excel Macros and file names

Question

2 questions on the same subject here, I'm learning a lot about macros and just unsure of how far I can take their abilities.

I'm trying to setup a macro to copy info from a file that I get emailed automatically into another file, and the 2 file names are my questions....

1.  The file I receive is named the same every time I get it, so when I open it from Outlook it is named something like xxxx (1).xls then xxxx(2).xls and so on.  How can I handle that in a macro?  Are there wildcards or anything like that allowed?

2.  The file I am pasting into gets saved at various stages into the same folder, but with a date on the end for bigger revisions, I am always pasting into the latest one.  For example xxxx06-14-2010.xlsb and xxxx07-03-2010.xlsb (along with many others) are out there, but I just need to paste into xxxx07-03-2010.xlsb.  Is there a way to programatically choose the file with the latest date?  Would using a new naming convention help?

The macro will be saved into my peronal.xlsb file, so it is not in any of the affected files.

Answer 1

I'm not 100% sure what you are asking for in Question 1 (it almost sounds like you want an Outlook macro rather than an Excel macro)... can you give us a more detailed description of what you want here? As for Question 2, I think this code will do what you want (just change the Path and BaseName variable assignments to what you actually are working with)...

Dim Path As String, BaseName As String, FileName As String, LatestFile As String

Path = "c:\Temp\Test"  '<== Notice the trailing backslash

BaseName = "xxxx"

FileName = Dir(Path & BaseName & "*.xlsb")

Do While Len(FileName)

  LatestFile = Path & FileName

  FileName = Dir()

Loop

MsgBox "Latest file name and path is.... " & LatestFile