Simplest code to open all files in a designated folder

This macro works perfectly to open 14 workbooks - whose names are listed in the Range("J3:J16") - and aggregate their data on Sheet 1. However I want it to open all the files in the folder ("330_09~03") no matter what the file names are.
Sub DoItAll()
Dim iPath As String, iFile As String, i As Long
iPath = "H:\Depts\css\A_ILS & Reliability\Reliability\1-CURRENT\System Reporting\SDAE\KAL\2013\Originals Data Files\330_09~03\"
For i = 3 To 16
   Workbooks.Open Filename:=iPath & ThisWorkbook.Sheets("Sheet1").Cells(i, 10).Value
Next i
ThisWorkbook.Activate
Sheets("All Files").Activate
Call AggregateFiles
End Sub

Open in new window

What's the absolute simplest way to replace my 3 "For..Next" lines and achieve the same result? They're all ".xls" files if that makes it any simpler.

Thanks,
John
LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Some sample code:

Public Sub ProcessWorkbooks()
   
   Dim FileName As String
   Dim SourceWorkbook As Workbook
   Dim SourceWorksheet As Worksheet
   
   Const Folder = "C:\Documents and Settings\Login Name\Desktop\File Folder"
   
   FileName = Dir(Folder & "\*.xls")
   Do While FileName <> ""
      Workbooks.Open Folder & "\" & FileName
      ' Add code here to work with each workbook
      For Each SourceWorksheet In ActiveWorkbook.Workheets
         ' Add code here to work with each worksheet
      Next SourceWorksheet
      ' Change True to False if only pulling information out of the workbooks
      ActiveWorkbook.Close True
      FileName = Dir
   Loop
   
End Sub

Kevin
0
 
SteveCommented:
DIR() is probably the simplest:

Sub DoItAll()
Dim iPath As String, iFile As String, i As Long
iPath = "H:\Depts\css\A_ILS & Reliability\Reliability\1-CURRENT\System Reporting\SDAE\KAL\2013\Originals Data Files\330_09~03\*.xlsx"
iFile = Dir(iPath) ' return into Dir all files specified using iPath
do while iFile <> ""
    Workbooks.Open Filename:=iFile
    Dir() ' nove to next iFile
loop
ThisWorkbook.Activate
Sheets("All Files").Activate
Call AggregateFiles
End Sub 

Open in new window

0
 
SteveCommented:
Kevin... workheets... oops!

Though mine needs some work too :( folder path needed in Workbooks.Open Filename:=iFile
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
zorvek (Kevin Jones)ConsultantCommented:
Sorry? Worksheets?

Just some sample code ;-)

Kevin
0
 
SteveCommented:
At least we both agree on using Dir() rather than FSO
0
 
zorvek (Kevin Jones)ConsultantCommented:
Well, that's debatable. I use it only because it's so convenient. But it's not without issues.

When the Dir function is in use all parent directories are locked preventing them from being renamed or deleted. The folders can be unlocked by calling Dir with an empty string:

   Dir ""

For example, the following code will fail with an error code 75 on the Kill statement:

   If Len(Dir("C:\Temp\*.*")) > 0 Then
      Kill "C:\Temp\*.*"
   End If
   Kill "C:\Temp"

To prevent the error, the Dir function must be forced to release the folder before it is deleted:

   If Len(Dir("C:\Temp\*.*")) > 0 Then
      Kill "C:\Temp\*.*"
   End If
   Dir ""
   Kill "C:\Temp"

The Dir function is one of the few commands that maintain a state. This means that the Dir function remembers the last call that was made and the last path and pattern that was passed to it. This is so that constructs such as this can be used to pull the list of all files from a directory that match a pattern:

   Dim FileName As String
   FileName = Dir("C:\Full\Path\To\Files\*.*")
   Do While FileName <> ""
      ' Work with file
      FileName = Dir
   Loop

The first invocation of Dir includes the passing of a directory and pattern. The value returned is the first matching file or an empty string if no matching file was found. The next invocation does not pass a directory and pattern and thus the Dir function continues with the original directory and pattern and returns the next matching file. This continues until the list of matching files is exhausted.

The problem with this approach is that, while the Dir function is being used, it cannot be used anywhere else in the program or the Dir routine's state will change. Dir is also frequently used to check if a file exists:

   If Len(Dir("C:\Full\Path\To\File.txt")) = 0 Then
      MsgBox "File is missing."
   End If

If this file exists checking code is run while the code above is looping through files, the Dir state will be changed and the loop will return erroneous results.

Avoiding the Problem

One way to avoid this problem is to use alternative techniques other than the Dir function to check if a file exists. But that strategy is more difficult than it seems because there are other uses for Dir that have have to be considered and used. Keeping track of all the alternaitves and making sure the Dir function is only used to list files in a directory is not that easy. Rather than find alternatives to all the other ways the Dir function can be used, it is easier to find a different way to loop through files and leave the other uses of Dir alone. To make the Dir function always work correctly when working with multiple files in a directory, focus just on getting the list of files and then operate on those files after the list is obtained. The code below illustrates how to do this with a collection:

   Dim Files As New Collection
   Dim FileName As Variant
   FileName = Dir("C:\Full\Path\To\Files\*.*")
   Do While FileName <> ""
      Files.Add FileName
      FileName = Dir
   Loop
   For Each FileName In Files
      ' Work with file
   Next FileName

Kevin
0
 
SteveCommented:
Well now, there's something I didn't know... that Dir() holds open the file to prevent changes like Kill. I cannot recollect if that has got me in the past, but is sure nice to know (I do like to know little pieces of info like that).

It makes me wonder...
Is a dictionary able to hold the results of Dir() such as :
dFile = Dir(path)
dic.add "results" , dFile

and would it hold the files in limbo in the same way?
0
 
zorvek (Kevin Jones)ConsultantCommented:
The Dir function only returns a string. Stuffing those strings into a collection or dictionary object doesn't tie up the folder or the file.

Kevin
0
 
[ fanpages ]IT Services ConsultantCommented:
At least we both agree on using Dir() rather than FSO

Do you have an Objection to using the File System method, Steve?

(PS. Did you see what I did there? etc.)
0
 
zorvek (Kevin Jones)ConsultantCommented:
One has to wonder why FP BFN Nigel is either completely MIA or completely engaged. Is there no happy medium in the middle?

;-)

And then there is John. He asks the question and then goes on holiday.
0
 
SteveCommented:
I have no Objection to using FSO, but the fun begins when having to explain the Reference and then how to do the objects etc... so not "simple"

I tend to switch between Dir() and FSO depending on use... which is likely why I hand't been hit by the situation Kevin was describing.

And I see it "O" I doo

:)
0
 
zorvek (Kevin Jones)ConsultantCommented:
Both are a pain.

I wrote my own generic file and folder class objects to do the work.

And, no, I'm not posting them here.
0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks, Kevin, that works great. I was able to adapt it further to suit my needs ... which of course always surprises ... and delights me.

Barman, your code looks like it would probably work but it bugged on "Dir() 'move to next iFile," and Kevin's worked right out of the box, so I just went with his. Please forgive me for not awarding you some points.

Thanks,
John
0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Hey I'm glad to see that in the 30 seconds it took me to award the points, you guys got a whole forum going. For what it's worth, here's what I ended up with. It opens 28 files from two different folders, appends their data in the macro-calling master file (13,000 rows worth), and closes all the files ... in less than 10 seconds. The power of code never ceases to amaze me. And to think that there are people out there in the age of DNA who still don't believe in God! And by "God," I don't mean just you, Kevin. :-)

Sub DoItAll()
Application.ScreenUpdating = False

  Folder = "H:\Depts\css\A_ILS & Reliability\Reliability\1-CURRENT\System Reporting\SDAE\KAL\2013\Originals Data Files\" & [H1].Value & "\"
   FileName = Dir(Folder & "\*.xls")
   Do While FileName <> ""
      Workbooks.Open Folder & "\" & FileName
      FileName = Dir
   Loop
ThisWorkbook.Activate

   Folder = "H:\Depts\css\A_ILS & Reliability\Reliability\1-CURRENT\System Reporting\SDAE\KAL\2013\Originals Data Files\" & [i1].Value & "\"
   FileName = Dir(Folder & "\*.xls")
   Do While FileName <> ""
      Workbooks.Open Folder & "\" & FileName
      FileName = Dir
   Loop
ThisWorkbook.Activate

Call AggregateFiles
Application.ScreenUpdating = True
End Sub

Open in new window

Thanks,
John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.