I need to consolidate data from many files in a directory with
filenames in the form of [variable length text name][4-digit
number].xls

For example:
alpha0104.xls
alpha0204.xls
beta0104.xls
gamma0204.xls
omicron0704.xls
etc...

There are also other files in the directory not of this format that I
will ignore.

In some cases, I need all the files of the same name, regardless of
the number. I can build a filename string like "alpha????.xls", which
works fine. Or, if I need all the files of the same number, I can use
"*0104.xls" which also works. However, if I want all the files in this
format that begin with the letter a, "a*????.xls" will not work
because it picks up other files that I don't want. I tried
"a*####.xls", which theoretically would work perfectly for my
application, but the # is apparently not a valid wildcard for
numeric-only characters in a filename. Is there some wildcard for
numeric characters in filenames, or can someone suggest a different
approach?

Thanks,
Jeff

If you are using the Dir command, just use the a*.xls and then ignore files
that don't meet your criteria.

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
om...
I need to consolidate data from many files in a directory with
filenames in the form of [variable length text name][4-digit
number].xls

For example:
alpha0104.xls
alpha0204.xls
beta0104.xls
gamma0204.xls
omicron0704.xls
etc...

There are also other files in the directory not of this format that I
will ignore.

In some cases, I need all the files of the same name, regardless of
the number. I can build a filename string like "alpha????.xls", which
works fine. Or, if I need all the files of the same number, I can use
"*0104.xls" which also works. However, if I want all the files in this
format that begin with the letter a, "a*????.xls" will not work
because it picks up other files that I don't want. I tried
"a*####.xls", which theoretically would work perfectly for my
application, but the # is apparently not a valid wildcard for
numeric-only characters in a filename. Is there some wildcard for
numeric characters in filenames, or can someone suggest a different
approach?

Thanks,
Jeff

I probably should have been more specific. I'm trying to come up with
a filename to use in a consolidate function:

Selection.Consolidate Sources:= _
"'\\Shc1\bill files\[" & cFileName & "]Summary'!R7C6",
Function:=xlSum, _
TopRow:=False, LeftColumn:=False, CreateLinks:=False

cFileName is the string I need to develop with the appropriate
wildcards as described in my previous post.

Thanks,
Jeff


(Jeff) wrote in message . com...
I need to consolidate data from many files in a directory with
filenames in the form of [variable length text name][4-digit
number].xls

For example:
alpha0104.xls
alpha0204.xls
beta0104.xls
gamma0204.xls
omicron0704.xls
etc...

There are also other files in the directory not of this format that I
will ignore.

In some cases, I need all the files of the same name, regardless of
the number. I can build a filename string like "alpha????.xls", which
works fine. Or, if I need all the files of the same number, I can use
"*0104.xls" which also works. However, if I want all the files in this
format that begin with the letter a, "a*????.xls" will not work
because it picks up other files that I don't want. I tried
"a*####.xls", which theoretically would work perfectly for my
application, but the # is apparently not a valid wildcard for
numeric-only characters in a filename. Is there some wildcard for
numeric characters in filenames, or can someone suggest a different
approach?

Thanks,
Jeff

Why doesn't Tom's suggestion work? Find all files a*.xls and discard
those that don't meet your criterion (such as if the last 4 characters
before the period are not numbers. If the variable Fname contains the
filename, then the untested
IsNumeric(Mid(Fname, InStr(1, Fname, ".") - 4, 4))
should do the job.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I probably should have been more specific. I'm trying to come up with
a filename to use in a consolidate function:

Selection.Consolidate Sources:= _
"'\\Shc1\bill files\[" & cFileName & "]Summary'!R7C6",
Function:=xlSum, _
TopRow:=False, LeftColumn:=False, CreateLinks:=False

cFileName is the string I need to develop with the appropriate
wildcards as described in my previous post.

Thanks,
Jeff


(Jeff) wrote in message . com...
I need to consolidate data from many files in a directory with
filenames in the form of [variable length text name][4-digit
number].xls

For example:
alpha0104.xls
alpha0204.xls
beta0104.xls
gamma0204.xls
omicron0704.xls
etc...

There are also other files in the directory not of this format that I
will ignore.

In some cases, I need all the files of the same name, regardless of
the number. I can build a filename string like "alpha????.xls", which
works fine. Or, if I need all the files of the same number, I can use
"*0104.xls" which also works. However, if I want all the files in this
format that begin with the letter a, "a*????.xls" will not work
because it picks up other files that I don't want. I tried
"a*####.xls", which theoretically would work perfectly for my
application, but the # is apparently not a valid wildcard for
numeric-only characters in a filename. Is there some wildcard for
numeric characters in filenames, or can someone suggest a different
approach?

Thanks,
Jeff

Tushar,

Thanks for the suggestion. Thanks also to Tom, whose suggestion I
didn't mean to disregard.

If I understand you correctly, you are saying I need to do some
preprocessing of the source filenames before executing the consolidate
method.

So I would find all a*.xls files, use the IsNumeric test to eliminate
the irrelavent ones, and then build a string array of the remaining
file names to pass to the Source parameter of the Consolidate method.
Am I on the right track?

Maybe I was just being lazy by trying to make this work all within the
Consolidate statement.

Best regards,
Jeff