Solved

executing macros from all files in a folder

Posted on 2016-10-04
5
39 Views
Last Modified: 2016-10-04
I have a folder with multiple files with a macro in each file. How can I execute all the macros in all the files one after other setting up a time gap say of 15 minutes.

all the files has the same macro.

Thanks in advance!!!
0
Comment
Question by:Nirvana
  • 3
  • 2
5 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 41828390
What's the name of the macro and where is it located?
0
 

Author Comment

by:Nirvana
ID: 41828482
I have used below code  and used the actual code, however, it is not working. infact, i need to press a button in each file how can Click the button on each file

Private Sub CommandButton1_Click()
    Module1.dakin
End Sub

Sub LoopThroughDirectory()
Application.DisplayAlerts = False
'Change this to your directory
MyPath = "C:\Users\Desktop\"
activefile = Dir(MyPath & "*.xls")
Do While activefile <> ""
   Workbooks.Open Filename:=MyPath & activefile
   'Here is the line that calls the macro below, passing the workbook to it
   DoSomething ActiveWorkbook
   ActiveWorkbook.Save
   ActiveWorkbook.Close
   activefile = Dir()
Loop
Application.DisplayAlerts = True
End Sub

Open in new window

0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 41828568
That button appears to be calling a sub named 'dakin' which is in Module1.

If that's the case there's no need to click the button, we can call the sub directly.
Sub LoopThroughDirectory()
Dim wb As Workbook
Dim MyPath As String
Dim activefile As String

    Application.DisplayAlerts = False
    'Change this to your directory
    MyPath = "C:\Users\Desktop\"
    activefile = Dir(MyPath & "*.xls")
    Do While activefile <> ""
        Set wb = Workbooks.Open(Filename:=MyPath & activefile)
        'Here is the line that calls the macro below, passing the workbook to it
        With wb
            Application.Run "'" & wb.Name & "'!dakin"


            .Close SaveChanges:=True
        End With

        activefile = Dir()
    Loop
    
    Application.DisplayAlerts = True
    
End Sub

Open in new window

1
 

Author Closing Comment

by:Nirvana
ID: 41828680
Brilliant
0
 

Author Comment

by:Nirvana
ID: 41828685
Thanks, Norie.. works perfectly!!!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question