open excel file via VBA with a twist

[mithu81]

Hi.
I have to make changes to about 100 xls files on a monthly basis.. These 100 files are in about 30 folders.

within each folder amongst other excel files there are 2 that start with "pre" and "post"
Example:

pre-dd352.xls and post-24356.xls

the text after pre and post are subject to change on a monthly basis. is there anyway i can get excel to open the pre and post files by looking for the words "pre" and use a wild card to get the rest. so basically "pre" wildcard ".xls" is what it should open?

I will be using Workbooks.Open Filename:="C:\dailyword\pre33d5.xls" command to open these books..

Any help would be great

 

[nbrcrunch]

That sounds like it could get dangerous. Aren't you looking at the potential of 30+ workbooks opening at the same time? I don't know a computer alive today that could handle that.

 

[mithu81]

nope I will open one.. make the changes i need to make and then close it out.. then open the next.

 

[westconn1]

use a loop with dir to find the filenames

spath = "C:\dailyword\"
sfile = dir(spath & "pre*.xls)
Workbooks.Open Filename:=spath & sfile

you would need to loop through the folders, within each iteration of the loop, loop through both the pre and post files separately
i don't know how you can loop through the folders as i don't know the folder structure, if they are all subfolders of some main folder you can loop recursively through the main folder and its sub folders