searching filenames

[sjharri]

Hi,

I have a master spreadsheet which contains a list of other spreadsheets
from which I want to extract data to a csv file. To do this the user
simply selects a spreadsheet and hits extract and the data from this
spreadsheet is extracted to a csv file. The location of the user
psreadsheet is defined in the master spreadsheet. Problem I have is
that some of the spreadsheets in the list have the date in the filename
therefore the filename is never the same.

For example: 000036 Valuation 31 May 2006.xls

I need to know how to do 3 things:

1. Search a specific directory for file containing the text 'Valuation'
in the filename.
2. Select the last updated file in a specific directory to be selected
by the master spreadsheet.
3. The master spreadsheet currently looks for the full name of the file
to process. When the user selects a specific spreadsheet the filename
is passed as a variable therefore the correct spreadsheet can be
processed. In the situation described what would I define as the
filename as I am in actual fact searching the filename for a text
string (e.g. Valuation not 000036 Valuation 31 May 2006.xls ). Can I
use some kind of wildcard around the text Valuation?

Thanks!!
Steve

 

[Tom Ogilvy]

Sub OpenFile()
Dim sname As String, dt As Date
Dim s As String, maxDt As Date
Dim bk As Workbook, sPath As String
sPath = "C:\Data\"
sname = Dir(sPath & "*valuation*.xls")
Do While sname <> ""
dt = FileDateTime(sPath & sname)
If dt > maxDt Then
maxDt = dt
s = sname
End If
sname = Dir
Loop

Set bk = Workbooks.Open(sPath & s)

End Sub