How to loop through excel files in folder

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How would i go about looping through all the excel files in a particular
folder. I think i need to use a FOR EACH statement, but have no idea how to
reference the collection of tables

Thanks in advance
 
Dim strFolder As String
Dim strFile As String

strFolder = "C:\Some Folder\Some Subfolder\"
strFile = Dir$(strFolder & "*.xls")
Do While Len(strFile) > 0

' At this point, you know that strFolder & strFile points to an existing
Excel file
' Do whatever you need to do with the file (as long as you do not use the
Dir
' function!)

strFile = Dir$()
End While
 
Why would you need to use Dir inside the Dir loop? There may be better
options.

If you're stuck, you could use API calls or, while I hate to suggest it, FSO
(File System Objects).
 
thank you i had to go with fso

i have a bat file run for each file in a dir and i made that on the end the
bat file should create some file,
and i had vb loop again until the file is created

by the way why are you hating this fso?
 
FSO introduces far more overhead than makes sense to me. There's only one
thing I've ever found that FSO can do that you can't do with VBA commands
(get the dates associated with directories). It's not too bad if you use
Late Binding. If you use Early Binding, though, you run the risk of
References problems in your application.
 
Hi Doug,

There's only one
thing I've ever found that FSO can do that you can't do with VBA commands
Click to expand...

Find out when the file was created and last accessed, as well as
modified?
 
John Nurick said:
Hi Doug,



Find out when the file was created and last accessed, as well as
modified?
Click to expand...

You're right, but I still prefer APIs for that.

Don't get me wrong: I use FSO when it's appropriate (like in VBScript). I
just don't see the point of it in VB or VBA.
 
Back
Top