Process many Excel files in VBA

JerryDal · #1 (**permalink**) 02-15-07, 19:57

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

shades · #2 (**permalink**) 02-15-07, 21:00

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.

gvee · #3 (**permalink**) 02-16-07, 07:25

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...

shades · #4 (**permalink**) 02-16-07, 13:40

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

JerryDal · #5 (**permalink**) 02-16-07, 13:55

Thanks shades. Your VBA solution to process every Excel file in a specific folder does the job.
Jerry

Internet Services

Web Development

Digital Marketing

Consumer Tech