Solved

Simplest code to open all files in a designated folder

Posted on 2013-06-07
14
326 Views
Last Modified: 2013-06-07
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
0
Comment
Question by:gabrielPennyback
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 24

Expert Comment

by:Steve
Comment Utility
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
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
Comment Utility
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
 
LVL 24

Expert Comment

by:Steve
Comment Utility
Kevin... workheets... oops!

Though mine needs some work too :( folder path needed in Workbooks.Open Filename:=iFile
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
Comment Utility
Sorry? Worksheets?

Just some sample code ;-)

Kevin
0
 
LVL 24

Expert Comment

by:Steve
Comment Utility
At least we both agree on using Dir() rather than FSO
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
Comment Utility
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
 
LVL 24

Expert Comment

by:Steve
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
Comment Utility
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
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
Comment Utility
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
 
LVL 24

Expert Comment

by:Steve
Comment Utility
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
Comment Utility
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
 
LVL 1

Author Closing Comment

by:gabrielPennyback
Comment Utility
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
 
LVL 1

Author Comment

by:gabrielPennyback
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now