Solved

Simplest code to open all files in a designated folder

Posted on 2013-06-07
14
363 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 39230144
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
ID: 39230145
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
ID: 39230150
Kevin... workheets... oops!

Though mine needs some work too :( folder path needed in Workbooks.Open Filename:=iFile
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39230155
Sorry? Worksheets?

Just some sample code ;-)

Kevin
0
 
LVL 24

Expert Comment

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

Expert Comment

by:zorvek (Kevin Jones)
ID: 39230175
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
ID: 39230197
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39230211
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 ]
ID: 39230273
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)
ID: 39230316
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
ID: 39230318
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)
ID: 39230328
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
ID: 39230499
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
ID: 39230523
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

733 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