Solved

executing macros from all files in a folder

Posted on 2016-10-04
5
46 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

680 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