Run Macro On Multiple Workbooks in Folder

[dgr]

Hello,
I'm using Excel 2013.

I've got many workbooks in a folder. I want to open all these workbooks one by one, run a macro, save the workbook & close it. I do not want to specify the names of the workbooks in the folder so that I can simply add / remove workbooks in the future without having to edit the macro. Therefore, the macro needs to detect all .xlsx in the folder automatically.

The macro will be stored in another workbook stored elsewhere in my pc. I visualize the macro to look something like this:

Sub test()

open the workbook in the specified folder
do some stuff
ActiveWorkbook.Save
ActiveWorkbook.Close
loop the macro until all workbooks have been processed
End Sub

How do I do this?

Thanks a lot for your help.

 

[lakshya2425]

Hello,
I'm using Excel 2013.

I've got many workbooks in a folder. I want to open all these workbooks one by one, run a macro, save the workbook & close it. I do not want to specify the names of the workbooks in the folder so that I can simply add / remove workbooks in the future without having to edit the macro. Therefore, the macro needs to detect all .xlsx in the folder automatically.

The macro will be stored in another workbook stored elsewhere in my pc. I visualize the macro to look something like this:

Sub test()

open the workbook in the specified folder
do some stuff
ActiveWorkbook.Save
ActiveWorkbook.Close
loop the macro until all workbooks have been processed
End Sub

How do I do this?

Thanks a lot for your help.

I don't know what u want to do , but i simply write a code to copy from each workbook in a folder into my master workbook



Sub getmydata()


Dim xpath As String
Dim fname As String
Dim wb As Workbook
Dim wb1 As Workbook
Dim ws As Worksheet
Dim lr As Long, lr1 As Long

Set wb = ThisWorkbook
xpath = "C:\Users\saurabh\Desktop\New folder"




fname = Dir(xpath & "\" & "*.xl??")





Do While fname <> ""


Set wb1 = Workbooks.Open(xpath & fname)

For Each ws In wb1.Worksheets

lr = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row
lr1 = wb.Sheets(ws.Name).Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1

ws.Range("A1:C" & lr).Copy wb.Sheets(ws.Name).Range("A" & lr1)

Next ws
wb1.Save
wb1.Close (False)
fname = Dir()
Loop






End Sub