Open Books with VBA based on certain string

B

brett.kaplan

Is there a way to look in a folder and if a file has a certain string
in it's name "Sep2006", it will open. I don't care what else it has in
the file name, but if that string is in there, I want it to open.

Basically, I'm going to have a sheet for say 10 people with their name
and Sep2006 in a folder. I want to then open 1 sheet at a time, take a
value from it, and aggregate it into a new sheet.

Thanks,
Brett
 
J

JLatham

Short answer: yes, I think. I presume you want the workbook that is going to
have the aggregated data placed into it do the opening and extracting from
the other workbooks?

Couple of ways to attack it, easiest is if the 'control' or new aggregate
workbook is in the same folder with the others. Then with VBA code you could
have it determine it's own location (and thus the location of the other
workbooks) and using the DIR() function in VBA, you would examine the
filenames of other workbooks in the same folder and when you found one with
Sep2006 within it that is of type .xls, then open it, grab the information
you need, place it into the control workbook, close it and look for another
until it runs out of them. Reiterative DIR() basically goes thru each
possible match until it runs out of possible matches, then returns an empty
string, so don't even have to worry about processing same book twice (during
one run of the code).

Code might look something like this:

Sub FindSpecialFiles()
Dim anyFile As String

anyFile = UCase(Dir(Left(ThisWorkbook.FullName, _
Len(ThisWorkbook.FullName) - _
Len(ThisWorkbook.Name)) & "*.xls"))

Do While anyFile <> ""
'remember strings are case sensitive
If InStr(anyFile, "SEP2006") Then
'....perform file open,
'....data extraction
'....data aggregation
'....close Sep2006 file
End If
anyFile = UCase(Dir) ' get next possible file
Loop

End Sub

I used UCase and made the comparison to "SEP2006" since strings are case
sensitive in VB and the UCase assures that the returned filename(s) are all
in uppercase.
 
B

brett.kaplan

Hi,

Thanks a lot for that! It does what I need in the first loop, but it
creates an infinite loop. How can I make the anyFile = UCase(Dir) to
at somepoint equal ""?

Thanks,
Brett
 
J

JLatham

It shouldn't be going into an infinite loop.

The first time we use the DIR() to set up anyFile outside of the loop (with
that long concatenation statement) anyFile will either be "" (empty string,
no space between the double-quotes) if no file was found that matched, in
which case the loop won't even begin.
But once in the loop, repeating the DIR statement without a new parameter
just repeats the search for the next file with matching name. So lets say
that you have 4 files with SEP2006 in their names:
file01_Sep2006.xls
File03_Sep2006.xls
file4_sep2006.xls
f2_sep2006.xls

The DIR may return them in any sequence, not necessarily their 'sorted
order' or the order in which they were saved to the drive.

So let's say that it returns them in 1,2,3 4 order just for example.
Outside of the loop, anyFile would get set to "FILE01_SEP2006.XLS" it would
not be zero length, so it would enter the loop. Then the Instr() test is
going to return a non-zero, positive value which will cause the stuff in the
If...Then statement block to execute.

The anyFile=UCase(DIR) will simply get the next (any) filename with .xls at
the end and it may not even be one of our SEP2006 files, it could be named
AUG2006.xls, but that would fail the InStr() test. But eventually UCase(DIR)
is going to pick up the names of the other files of interest. Finally it's
going to run out of the list of .xls files in the folder to examine, and when
it does that, that is when it returns an empty string and it will fall out of
the loop.

You can test what is being returned each time by putting a MsgBox statement
just ahead of the Do While statement as
MsgBox "Starting With: " & anyFile

then put another down right after
anyFile = UCase(DIR)
as
MsgBox "Found Excel File: " & anyfile

so you'll be able to verify that it's returning different file name each
time through the loop.
 
Top