Process all Files in a folder

Many a times we comeacross a situation where we want to process all the excel sheets or some of the excel files in a folder and it becomes a tedious job to get it done taking hours and days sometimes. I cameacross the following code form vbaexpress.com where this code will take care of all the excel files in a folder. I hope this helps. I am thankful to “Ken Puls”, as I have learned many things from yhis posts and tips. — Selva V Pasupathy

 

Option Explicit

Sub GetAllFiles_In_a_Folder(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

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: