14

I have a folder where I receive 1000+ excel files on daily bases they all are same format and structure. What I want to do is run a macro on all 100+ files on daily bases ?

Is there way to automate this ? So I can keep running that same macro on 1000+ files daily.

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
user1570210
  • 1,169
  • 12
  • 26
  • 37
  • 2
    Try this http://stackoverflow.com/questions/3375175/how-do-i-open-all-the-excel-files-one-by-one-and-run-a-macro – Meherzad Feb 08 '13 at 05:54
  • possible duplicate of [Loop through files in a folder using VBA?](http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba) – brettdj Feb 08 '13 at 10:54

6 Answers6

17

Assuming that you put your files in "Files" directory relative to your master workbook your code might look like this:

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

In this example DoWork() is your macro that you apply to all of your files. Make sure that you do all your processing in your macro is always in the context of the wb (currently opened workbook).

Disclaimer: all possible error handling skipped for brevity.

peterm
  • 91,357
  • 15
  • 148
  • 157
  • Sub ProcessFiles() Dim Filename, Pathname As String Dim wb As Workbook Pathname = ActiveWorkbook.Path & "C:\Macro\" 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 Range("A1").Select ActiveCell.FormulaR1C1 = "Name" Range("B1").Select ActiveCell.FormulaR1C1 = "Anil" Range("A2").Select End With End Sub – user1570210 Feb 09 '13 at 02:13
  • @user1570210 It's not working for you because instead of `Range("A1").Select ActiveCell.FormulaR1C1 = "Name"` you should use `.Worksheets(1).Range("A1").FormulaR1C1 = "Name"`. You didn't specify what worksheet you are working on. And there's no need to select ranges, at least in this example. Just address them directly as in the answer. Of course you need to adjust the name or index of your worksheet. It might be `.Worksheets("Sheet1")` or whatever have you. – peterm Feb 09 '13 at 03:19
  • @user1570210 Also instead of `Pathname = ActiveWorkbook.Path & "C:\Macro\"` it seems that is should be `Pathname = "C:\Macro\"` – peterm Feb 09 '13 at 09:05
3

A part of the question might be how do I run this on 1000 files?... Do I have to add this macro to all 1000 workbooks?

One way to do this is to add your macro's centrally to the file PERSONAL.XLSB (sometimes the extension might be different). This file will be loaded in the background every time you start Excel and makes your macro's available at any time.

Initially the PERSONAL.XLSB file will NOT be there. To automatically create this file, just start recording a "dummy" macro (with the record button on the left-bottom of a spreadsheet) and select "Personal Macro Workbook" to store it in.

After recording your macro, you can open the VBA editor with Alt+F11 and you will see the PERSONAL.XLSB file with the "dummy" recorded macro.

I use this file to store loads of general macro's which are always available, independent of which .xlsx file I have open. I have added these macro's to my own menu ribbon.

One disadvantage of this common macro file is that if you launch more than one instance of Excel, you will get an error message that the PERSONAL.XLSB file is already in use by Excel instance Nr. 1. This is no problem as long as you do not add new macro's at this moment.

Peter L.
  • 7,276
  • 5
  • 34
  • 53
Robert Ilbrink
  • 7,738
  • 2
  • 22
  • 32
  • 1
    I think it is ALT+F11 to switch between main Excel and the VBA environment. – Olle Sjögren Feb 08 '13 at 08:53
  • @Olle, You are right! And Peter, Thank you for correcting this. My error trying to write something too fast, just before leaving the house... – Robert Ilbrink Feb 08 '13 at 11:53
  • this code and seems like its not working Sub ProcessFiles() Dim Filename, Pathname As String Dim wb As Workbook Pathname = ActiveWorkbook.Path & "C:\Macro\" 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 Range("A1").Select ActiveCell.FormulaR1C1 = "Name" Range("B1").Select ActiveCell.FormulaR1C1 = "Anil" Range("A2").Select End With End Sub – user1570210 Feb 09 '13 at 02:14
2

Thank you very much for this 

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

    Pathname = ActiveWorkbook.Path & "C:\Users\jkatanan\Desktop\20170206Glidepath\V37\"
    Filename = Dir(Pathname & "*.xls")
    Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & Filename)
        BSAQmacro 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
cyboashu
  • 10,196
  • 2
  • 27
  • 46
Jazia Katanani
  • 21
  • 1
0

Instead of passing the values to DoWork one can also run the jobs in Processfiles().

Sub ProcessFiles()

    Dim Filename, Pathname As String
    Dim wb1 As Workbook
    Dim wb2 As Workbook

    Dim Sheet As Worksheet
    Dim PasteStart As Range
    Dim Counter As Integer

    Set wb1 = ActiveWorkbook
    Set PasteStart = [RRimport!A1]

    Pathname = ActiveWorkbook.Path & "\For Macro to run\"
    Filename = Dir(Pathname & "*.xls")
    Do While Filename <> ""
        Set wb2 = Workbooks.Open(Pathname & Filename)
        For Each Sheet In wb2.Sheets
                With Sheet.UsedRange
                .Copy PasteStart
                Set PasteStart = PasteStart.Offset(.Rows.Count)
            End With
        Next Sheet
        wb2.Close
        Filename = Dir()
    Loop
End Sub
Stewie Griffin
  • 14,889
  • 11
  • 39
  • 70
Thavachelvan Kesavan
  • 1
0
Sub ProcessFiles()
    Dim Filename, Pathname As String
    Dim wb As Workbook

    Pathname = ActiveWorkbook.Path & "\C:\Users\20098323\Desktop\EXCL\"
    Filename = Dir(Pathname & "*.xlsx")
    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

While running this code its showing bad file name or number. i have stored my all file in ("\C:\Users\20098323\Desktop\EXCL\") EXCL folder

Bhavik Modi
  • 13
  • 4
  • You should ask a new question rather than submitting it as an answer to an old one. But here's a hint: `ActiveWorkbook.Path & "\C:\Users\20098323\Desktop\EXCL\"` this is your problem because it appends your path to the path where your macro workbook is. – arcadeprecinct Jun 02 '16 at 06:48
0

This isn't an exact answer to the question, since I was just trying to run a single script on any file that I opened and couldn't get that to work, but I thought this might help others like me. It worked once I moved the code into a Module in the Visual Basic for Applications dialog box (go to "Insert" then "Module"). Once I added my VBA code to a module, I was able to open any other file in Excel (even a CSV file) and go to Macros, and run the Macro from the other file (that contains the Module with the code) on the file that I had open.

B-Rad
  • 201
  • 3
  • 5