I am using a macro to load several text files that have lots of saved data
in them. It is way to time consuming to load each file alone, as there are a
lot of them. My problem lies in the fact the files names are never the same
for each data set, as we have a random number generator to ensure our test
data is statistically correct. This leaves me with a bunch of files I need
to load in order, but each of them has a random number in the file name. Is
there any way to run a macro and load files that can overlook certain
characters in the file name? Example: my file names are P015 and P026. I
want to be able to overlook the 01 and 02 part and just tell Exceel to see
P**5 and P**6. Is there a way to do this? Or an alrernative way to load
several files that have random numbers in the file names - while still
needing them to be in order. I am using Excel Prof. 2003. Any help is
greatly appreciated...

Are all the files in one folder?

If yes:

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim TempWkbk As Workbook

'change the folder here
myPath = "C:\my documents\excel\test\"
If myPath = "" Then Exit Sub
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.txt")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile < ""
If LCase(myFile) Like LCase("p##5.txt") _
Or LCase(myFile) Like LCase("p##6.txt") Then
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
End If
myFile = Dir()
Loop

If fCtr 0 Then
For fCtr = LBound(myNames) To UBound(myNames)
Workbooks.OpenText Filename:=myPath & myNames(fCtr)
Set TempWkbk = ActiveWorkbook
'do your stuff
TempWkbk.Close savechanges:=False
Next fCtr
End If

End Sub


BWimer wrote:

I am using a macro to load several text files that have lots of saved data
in them. It is way to time consuming to load each file alone, as there are a
lot of them. My problem lies in the fact the files names are never the same
for each data set, as we have a random number generator to ensure our test
data is statistically correct. This leaves me with a bunch of files I need
to load in order, but each of them has a random number in the file name. Is
there any way to run a macro and load files that can overlook certain
characters in the file name? Example: my file names are P015 and P026. I
want to be able to overlook the 01 and 02 part and just tell Exceel to see
P**5 and P**6. Is there a way to do this? Or an alrernative way to load
several files that have random numbers in the file names - while still
needing them to be in order. I am using Excel Prof. 2003. Any help is
greatly appreciated...

--

Dave Peterson