Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Unanswered: Process many Excel files in VBA

    I have similarly formatted daily Excel files in a directory c:\Data\Transactions. From a main workbook or what I call a template, I want to open each one and store the data. Each file has the date as part of the file name.

    How can I capture the names of all the files in one directory from VBA. I will put the file names in an array and open each one, copy the data, then close the file.

    Jerry

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    If that is all you have in that directory then I don't think you need to put them in an aray through VBA. Let me find something that goes through every file in a specified directory. It might not be until tomorrow.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    There have been a few similar questions in the MSAccess forums - might be worth a look.
    I think Dir() works in Excel, so there's a start...
    George
    Home | Blog

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Code:
    Sub ProcessAll(sPath As String) 
        Dim Wb As Workbook, sFile As String 
         
        sFile = Dir(sPath & "*.xls") 
         'Loop through all .xls-Files in that path
        Do While sFile <> "" 
             
            Set Wb = Workbooks.Open(sPath & sFile) 
             
             'Do something with that Workbook, insert whatever you want to do here
            Debug.Print Wb.Name 
             'You can save it, if you like, here it's not saved
            Wb.Close False 
             
            sFile = Dir 
        Loop 
    End Sub
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Thanks shades. Your VBA solution to process every Excel file in a specific folder does the job.
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •