Solved

Excel97 - Iterate Files

Posted on 2002-04-02
10
190 Views
Last Modified: 2008-03-03
Could someone help me please???

I want to create a file with the sole purpose of copying and filtering information from other files. All the files that contain the source data will be of exactly the same format. These will be the only files in the source directory. This source directory will be given as a direct input on the new file.

What I need to know is how to I open each file from the source directory in turn using VBA code? I do not want to supply each individual file name in the new file. I would like it to automatically open EVERY file in the directory indicated. Thus, any new files WILL be included.

Thanks.
0
Comment
Question by:SHardy
[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
  • 3
10 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 6912729
Hi SHardy,

do you need this in one loop? i mean have all the files open at once or do walk through them in turn?
0
 
LVL 1

Author Comment

by:SHardy
ID: 6912738
What I was after was to open them in turn. I think I have come up with something now. I found the "FileSearch" class in the object browser, and have setup a small test as follows:

Sub test()

sourcepth = [SourcePath]

Sheets("SourceFiles").Select
Range("A4:A65536").ClearContents
Range("A4").Select

With Application.FileSearch
    .NewSearch
    .LookIn = sourcepth
    .SearchSubFolders = True
    .FileName = ""
    .MatchTextExactly = False
    .FileType = msoFileTypeAllFiles
End With

With Application.FileSearch
    If .Execute() > 0 Then
        For i = 1 To .FoundFiles.Count
            ActiveCell.Value = .FoundFiles(i)
            ActiveCell.Offset(1, 0).Select
        Next i
    Else
        MsgBox "There were no files found."
    End If
End With

End Sub

This gives me a list of files from the folder that I can then work through one at a time. This should do for what I was after, but if you have any different suggestions then please feel free.

Thanks.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6912806
that's what i had in mind ;) so you solved it yourself
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 44

Accepted Solution

by:
bruintje earned 50 total points
ID: 6912824
i can bring up something from my PAQ's


Sub GetFilesDir()
Dim strFilesInDir As String
Dim i As Integer

strFilesInDir = Dir("C:\temp\*.xls")    'insert your directory name

    'here, you can use wildcards
    'get different file types

Sheets(1).Select

i = 1
  Do Until strFilesInDir = ""
      Cells(i, 1).Value = strFilesInDir
      i = i + 1
     strFilesInDir = Dir
  Loop
End Sub

HTH:O)Bruintje
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6912825
that method uses the native VB functions instead of above but that should result in the same

Good Luck
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6912937
Sub GetExcelFilesDir()
Dim strFilesInDir As String
Dim i As Integer

strFilesInDir = inputbox("Name of the directory to look in", "PathName", "C:\My Documents")

strFilesInDir = Dir(strFilesInDir & "\*.xls")    'insert your directory name

   'here, you can use wildcards
   'get different file types

Sheets(1).Select

i = 1
 Do Until strFilesInDir = ""
     Cells(i, 1).Value = strFilesInDir
     i = i + 1
    strFilesInDir = Dir
 Loop
End Sub

This will ask for the path name first

you can ask a 0-point Q in the community support to refund your points and let me have this PAQ by accepting my comment as answer ;)
0
 
LVL 1

Expert Comment

by:Computer101
ID: 6918501
Points reduced per questioners request.

Computer101
E-E Moderator
0
 
LVL 1

Author Comment

by:SHardy
ID: 6920254
Bruintje,

Thanks for your comments, and I preferred your method anyway so you can still have some points.

Cheers.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6920262
Thanks for that, there are always more ways to solve the same problem
0
 
LVL 1

Author Comment

by:SHardy
ID: 6920272
Shame they're not always that easy to find.
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

630 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