Open File Using A Wildcard

[jmdaniel]

I am trying to write a macro that will be run weekly, and will open, one by
one, a series of files, extract a certain range of data, paste to a master
file, and close each source file. I cannot convince the requestor to leave
the date out of the file name, so the names of the files changes each week.

Here is a piece of the code:

Workbooks.Open Filename:= _
"C:\EnterpriseServices\ES Dashboard Laurie B 08-23-2007.xls"
Rows("4:40").Select
Selection.Copy

I tried placing an asterisk right after the initial of the person's last
name, (the "B" in this example), but of course this did not work.

Is what I am trying to do possible? If so, how? Thanks in advance!

 

[Bob Umlas]

How about something like thie:

Sub GetFiles()
With Application.FileSearch
.NewSearch
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count
If InStr(.FoundFiles(i), "C:\EnterpriseServices\ES Dashboard
Laurie B") > 0 Then
Workbooks.Open .FoundFiles(i)
'do your thing here
Workbooks(Dir(.FoundFiles(i))).Close True
End If
Next
End With
End Sub

Bob Umlas
Excel MVP

 

[Tom Ogilvy]

Assuming if there is a file with a name of this type, it is the correct file
then:

Dim sPath as String, sn1 as String, sn as String
spath = "C:\EnterpriseServices\"
sn1 = "ES Dashboard Laurie B "

sn = dir(spath & sn1 & "*.xls")
if sn = "" then exit sub

Workbooks.Open Filename:= _
spath & sn
Rows("4:40").Select