searching filenames

[sjharri@gmail.com]

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

--
Regards,
Tom Ogilvy


"sjharri@gmail.com" wrote:

> 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
>
>