Solved

executing macros from all files in a folder

Posted on 2016-10-04
5
30 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
Comment Utility
What's the name of the macro and where is it located?
0
 

Author Comment

by:Nirvana
Comment Utility
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
Comment Utility
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
Comment Utility
Brilliant
0
 

Author Comment

by:Nirvana
Comment Utility
Thanks, Norie.. works perfectly!!!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
Outlook Free & Paid Tools
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now