I need to learn a new technique to use for my next activity.

In a macro I need to find all the files in a folder that contain a
string (specified by the user at the start of the macro), typically a
date code and open that file and then do some processing on it.

For instance:

If the folder C:\monthly_reports contains the following files:

Name_1 20090905.xls
Name_2 20090905.xls
Name_3 20090905.xls
Name_1 20090922.xls
Name_2 20090922.xls
Name_3 20090922.xls

and the user asks for reports that contain the string "20090922" in
the file name would open (one at a time) the last three files in the
above folder.

I suspect this is pretty simple but something I haven't done yet.


John Keith

index = 0
sData ="*20090905*"
sFilename = DIR("C:\monthly_reports\*.xls"
do until sFilename = ""
if sFilename LIKE sDate then
; do something
index = index +1
cells(index,"A")= sFilename
end if
sFilename = DIR()
loop

"John Keith" wrote:

I need to learn a new technique to use for my next activity.

In a macro I need to find all the files in a folder that contain a
string (specified by the user at the start of the macro), typically a
date code and open that file and then do some processing on it.

For instance:

If the folder C:\monthly_reports contains the following files:

Name_1 20090905.xls
Name_2 20090905.xls
Name_3 20090905.xls
Name_1 20090922.xls
Name_2 20090922.xls
Name_3 20090922.xls

and the user asks for reports that contain the string "20090922" in
the file name would open (one at a time) the last three files in the
above folder.

I suspect this is pretty simple but something I haven't done yet.


John Keith

On Thu, 24 Sep 2009 07:29:02 -0700, Patrick Molloy
wrote:

index = 0
sData ="*20090905*"
sFilename = DIR("C:\monthly_reports\*.xls"
do until sFilename = ""
if sFilename LIKE sDate then
; do something
index = index +1
cells(index,"A")= sFilename
end if
sFilename = DIR()
loop

Patrick,

Thank you! I'll try it shortly.

This group is fantastic!


John Keith

On Thu, 24 Sep 2009 07:29:02 -0700, Patrick Molloy
wrote:

index = 0
sData ="*20090905*"
sFilename = DIR("C:\monthly_reports\*.xls"
do until sFilename = ""
if sFilename LIKE sDate then
; do something
index = index +1
cells(index,"A")= sFilename
end if
sFilename = DIR()
loop

Patrick,

It worked great, thank you. You also alerted me the to function "LIKE"
which is new to me.

For the sake of anyone who wants to use this same technique there is a
typo, "sDate" should be "sData".


John Keith

sData ="*20090905*"
....
....
if sFilename LIKE sDate then

It worked great, thank you. You also alerted me the to function "LIKE"
which is new to me.

I also like the Like function<g... it is very flexible; but, I think in
this situation, I think I would use a simple InStr test instead, mainly
because InStr is a more efficient function than the Like operator (it
executes faster than the Like operator does which can be important in large
loops).

sData = "20090905"
.....
.....
If InStr(sFilename, sData) 0 Then

If you are not completely familiar with the InStr function, you should look
it up in the help files... there are optional arguments which allows you to
make the search case insensitive (which is not important in your current
needs, of course).

--
Rick (MVP - Excel)

On Thu, 24 Sep 2009 13:17:38 -0400, "Rick Rothstein"
wrote:

I also like the Like function<g... it is very flexible; but, I think in
this situation, I think I would use a simple InStr test instead, mainly
because InStr is a more efficient function than the Like operator (it
executes faster than the Like operator does which can be important in large
loops).

Hi Rick,

Thank you for the comments. I am familiar with the Instr fucntion, in
fact it seems to be in heavy use in my recent work.

My initial test using the LIKE function was doone with a folder that
only had 5 files so speed issues were not evident. The folder I'll use
this on is currently ~200 files and grows at a rate of about 15/month
so I'll get a chance to evaluate the speed soon.


John Keith

good spot re my typo - my keyboard does have a habit of doing that!

you could also use an IF statement

IF RIGHT(sFilename,12) = sData & ".xls" Then

worth testing large folders to see which is faster, this or the INSTR()


best regards

"John Keith" wrote:

On Thu, 24 Sep 2009 13:17:38 -0400, "Rick Rothstein"
wrote:

I also like the Like function<g... it is very flexible; but, I think in
this situation, I think I would use a simple InStr test instead, mainly
because InStr is a more efficient function than the Like operator (it
executes faster than the Like operator does which can be important in large
loops).

Hi Rick,

Thank you for the comments. I am familiar with the Instr fucntion, in
fact it seems to be in heavy use in my recent work.

My initial test using the LIKE function was doone with a folder that
only had 5 files so speed issues were not evident. The folder I'll use
this on is currently ~200 files and grows at a rate of about 15/month
so I'll get a chance to evaluate the speed soon.


John Keith

On Mon, 28 Sep 2009 08:18:02 -0700, Patrick Molloy
wrote:

good spot re my typo - my keyboard does have a habit of doing that!

My keyboard does that a LOT also.

you could also use an IF statement

IF RIGHT(sFilename,12) = sData & ".xls" Then

worth testing large folders to see which is faster, this or the INSTR()

Unfortunately the filenames are searching are more complicated than
you assumed.

Thanks for the tips.



John Keith