opening multiple files from within a macro

[Srikanth]

Hello,

I want to be able to run the same macro on say all the excel files in
a given folder. (without me having to select each file). I want to be
able to select the appropriate folder(that contains all the excel
files) and the macro should do the rest. What is the best way to do
that ? Please help.

Thanks

Srikanth

 

[Tom Ogilvy]

Dim sPath as String, sName as String
Dim bk as Workbook
sPath = "C:\Myfolder\"
sName = Dir(sPath & "*.xls")
do while sName <> ""
set bk = workbooks.open(sPath & sName)
. . . process bk
bk.close Savechanges:=False ' or true
sName = Dir
Loop

 

[Myrna Larson]

Hi, Tom:

In my experience, if, between calls to Dir$, you make any changes to the
directory, like saving a file, it gets "mixed up" and may skip a file or
process one twice. This may not be a problem if you are only opening the file,
then closing it without resaving.

To avoid any problems, I usually use a first Do/Loop to get all of the file
names into an array, then a 2nd For/Next loop to retrieve the names from the
array and process the files.

I do this often enough that I wrote a sub to get the file names, so my code
would look like this:

Dim sPath as String
Dim sName as String
Dim bk as Workbook
Dim FileList() As String
Dim i As Long
Dim n As Long

sPath = "C:\Myfolder\"
sName = "*.xls"
n = GetFileList(sPath & sName, FileList())

For i = 1 to n 'NB: loop won't execute if n = 0
set bk = workbooks.open(sPath & FileList(i))
'process bk
Next i


~~~~~~~~~~~~~~~~~~~~~

Function GetFileList(Pattern As String, FileNames() As String) As Long
Dim f As String
Dim n As Integer

n = 0
Erase FileNames()

f = Dir$(Pattern)
Do While Len(f)
n = n + 1
ReDim Preserve FileNames(1 To n) As String
FileNames(n) = f
f = Dir$()
Loop

GetFileList = n

End Function 'GetFileList

 

[Tom Ogilvy]

I am aware of this as well, although it has never caused me any problems
that I recall. Normally I write the array approach, but then it gets ignored
because someone posts code like I did (which is simpler), so I figured if
the user had problems, they would post back. the KB article on this only
cites xl2000.

So have you truly experienced problems or you do you just believe that it
could and avoid them?

 

[Srikanth Ganesan]

Hi Tom and Myrna,

I still have a problem. When you define sName = "*.xls" it seems to
search for a file with a name *.xls instead of using the * as a
wildcard. Please help.

Srikanth

 

[Tom Ogilvy]

Not for me:

from the immediate window:

? dir("c:\data\*.xls")
KZ081-Default Survey1.xls

or
spath = "C:\data\"
sname = Dir(spath & "*.xls")
? sname
KZ081-Default Survey1.xls

 

[Myrna Larson]

Or (obviously!) for me, either.

 

[mike s]

Myrna....

I've looked at your code...relatively simple. I've also tried understanding
Bob Philips code in thread: Re: User Input to open multiple files - much
more difficult for me to understand.

my particular needs are as follows:

opening several csv files from multiple folders.
one file at a time, I need to modify each file and then close each file as
an excel file (preferably in the same location as the csv file).
(Ultimately, I could move all the files into one folder as a temporary
work-around, but this wouldn't be my preference)

By the way, do you have any recommendations for any particular resources for
gaining ground in VBA (particularly for excel)?

Thank you.