Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching filenames
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching filenames
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 " 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filenames and formulae | Excel Worksheet Functions | |||
Searching, matching then searching another list based on the match | Excel Discussion (Misc queries) | |||
getting filenames | Excel Programming | |||
Getting filenames via VBA | Excel Programming | |||
Filenames In Loops | Excel Programming |