Macro to open files from a partial file name ?

[Rich]

Hi,

I have an external application which dumps data to text files which I then
work on using excel. I'm trying to write a macro to automate the batch
conversion process, but I have a problem because the external application
names the files with a date appendage (see example below) :-

B_Report_011103.txt

I do need to keep the date in the excel file name if possible.

How can I get excel to open any filename begining with the string "B_Report"
? Is there any way of adding a wildcard to the file name string ?

Rich

 

[Michael Bednarek]

I have an external application which dumps data to text files which I then
work on using excel. I'm trying to write a macro to automate the batch
conversion process, but I have a problem because the external application
names the files with a date appendage (see example below) :-

B_Report_011103.txt

I do need to keep the date in the excel file name if possible.

How can I get excel to open any filename begining with the string "B_Report"
? Is there any way of adding a wildcard to the file name string ?

The FileSearch object was probably made for this kind of thing:
..FileName="B_Report*.txt"
But how do you prevent yesterday's file being found tomorrow?

That external application must have some rationale in its file name
creation, like today's ddmmyy (which is quite an unfortunate scheme).
Couldn't your macro replicate that:
Filename = "B_Report_" & Format(Now(), "ddmmyy") & ".txt"

 

[Bob Phillips]

Rich,

Here is an example
With Application.FileSearch
.NewSearch
.LookIn = "c:\MyDirectory"
.SearchSubFolders = True
.Filename = "B_Report*"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
.Execute

For i = 1 To .FoundFiles.Count
Debug.Print .FoundFiles(i)
Next

End With

You could also get input for the date if you want to be more specific and
append that.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)