Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

executing macros from all files in a folder

Posted on 2016-10-04
5
Medium Priority
?
55 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 34

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 34

Accepted Solution

by:
Norie earned 2000 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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

730 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