Macro Open File w/ Wildcard

[automationamateur]

VBA newbie and first post, so apologies ahead of time. I have 4 weekly .xls files emailed to me (which i save to C:\Documents and Settings\adam.r.mcreynolds\My Documents\Roger\COPS\new weekly files\). These files are deleted every week and 4 new files are added when received (4 files for 4 different regions, 'east', 'cent', 'flor', 'west'). The files names change week to week, for example one week they will be 'open bob east 12-27' the next week they will be 'open bob east 01-03' etc.

I am attempting to write a macro that copies the data from each file and pastes it into a corresponding master worksheet (this master worksheet also changes name every week). I can do this simple enough with a static file name but not with a changing name. I have read the other posts on this same topic and tried for hours to no avail. Below is what I have for a static file name solution (I don't need assistance on the copy/paste portion just getting the files open) How can I get the same results as below but with a wildcard (ie Workbooks.Open "C:\Documents and Settings\adam.r.mcreynolds\My Documents\Roger\COPS\new weekly files\open bob east*")

Workbooks.Open "C:\Documents and Settings\adam.r.mcreynolds\My Documents\Roger\COPS\new weekly files\open bob east 12-27"
Workbooks.Open "C:\Documents and Settings\adam.r.mcreynolds\My Documents\Roger\COPS\new weekly files\open bob cent 12-27"
Workbooks.Open "C:\Documents and Settings\adam.r.mcreynolds\My Documents\Roger\COPS\new weekly files\open bob flor 12-27"
Workbooks.Open "C:\Documents and Settings\adam.r.mcreynolds\My Documents\Roger\COPS\new weekly files\open bob west 12-27"


THANKS AHEAD OF TIME!!!

 

[Zardax]

Hi, Here's some code you can use to 'trawl' a folder for files with a specific extension. Once one of these files are found, you can then work your magic.

Cheers

    'for this method to work you must add the reference "Microsoft Scripting RunTime" ('Tools, References')
Sub getAllExcelFiles()
    'setup a file object handler
Dim fs As New FileSystemObject
Dim fFiles As Files
    'collect all the files within the specified directory
Set fFiles = fs.GetFolder("C:\TEMP").Files
    'set a file object handler
Dim fFile As File
    'check to see the type of each file
For Each fFile In fFiles
        'VBA doesn't have a built in file extension method, so look at the file name string
        'use short string as we don't need the whole path, just the file name
    If Mid(fFile.ShortName, Len(fFile.ShortName) - 2) = "XLS" Then
        'There is a match go do stuff with this file
        MsgBox "here's an excel file " & fFile.Name
    Else
        'this file doesn't match our criteria, so move on to the next file.
    End If
Next
MsgBox "Done Checking Files", vbOK, "My File Checker"
End Sub

 

[automationamateur]

Hmmm, Thanks for the quick post. I'll give this a try but from what I've been reading on related posts there are several ways to go about this and I'm thinking this may not be the best way for me. Any other suggestions out there?