VBA for multiple workbooks in a directory

Hey Guys,

Let me explain what I am trying to achieve:

I've multiple excel workbooks in a folder. I've to do same task in all of the work books.

Running the same script from Visual Basic Editor for each & every excel work book is very tedious.

How can I run the VBA code for every workbooks in a directory?

Suppose pass a parameter as folder name with path and the VBA code executes for every excel work book present there.

Is this possible? While surfing I found that we can use Visual Basic Script in such cases, I dont have any idea to do this,
please explain.

Thank You!!!

Hi,

should be possible if you know coding in VBA !

Start to read Dir function inner VBA help …

Hello Marc L,

I think this is also possible using Visual Basic Script. But I am not sure.

Thank You.

VBA code to open multiple files in same folder and perform same macros

Sub OpenAndClearContentsInColumnAA()
Dim strF As String, strP As String
Dim wb As Workbook
Dim ws As Worksheet


'Edit this declaration to your folder name
strP = "C:\Documents and Settings\MANAGER\Desktop\Dazzas Files" 'change for the path of your folder


strF = Dir(strP & "\*.xlsm") 'Change as required


Do While strF <> vbNullString


    Set wb = Workbooks.Open(strP & "\" & strF)
    Set ws = wb.Sheets(1) 'uses first sheet or if all the same names then ws.Sheets("yoursheet")
    ws.Range("AA2:AA" & ws.Range("AA" & ws.Rows.Count).End(xlUp).Row).ClearContents
    wb.Close True
    
    strF = Dir()
Loop


End Sub

and another VBA Code To Open Multiple Excel Files;

Const FOLDER As String = "C:\\test\" 
 
Sub ProcessEachFileInFolder() 
     
    On Error Goto ErrorHandler 
     
    Dim fileName As String 
     
    fileName = Dir(FOLDER, vbDirectory) 
     
     ' loop through folder, only process .xls files
    Do While Len(fileName) > 0 
        If Right$(fileName, 4) = "xlsb" Then 
            Call ProcessFile(fileName) 
        End If 
        fileName = Dir 
    Loop 
     
ProgramExit: 
    Exit Sub 
ErrorHandler: 
    MsgBox Err.Number & " - " & Err.Description 
    Resume ProgramExit 
End Sub 
 
Sub ProcessFile(fileName As String) 
     
    Dim currentWkbk As Excel.Workbook 
     
     ' open workbook
    Set currentWkbk = Excel.Workbooks.Open(FOLDER & fileName) 
     
     ' do whatever you need to do with currentWkbk
     
End Sub

If you want to apply all excel files ( including closed );

Sub ProcessFiles()
    Dim Filename, Pathname As String
    Dim wb As Workbook

    Pathname = ActiveWorkbook.Path & "\Files\"
    Filename = Dir(Pathname & "*.xls")
    Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & Filename)
        DoWork wb
        wb.Close SaveChanges:=True
        Filename = Dir()
    Loop
End Sub

Sub DoWork(wb As Workbook)
    With wb
        'Do your work here
        .Worksheets(1).Range("A1").Value = "Hello World!"
    End With
End Sub