CODEWITHSUNDEEP
excelvba
Richard Herron
Richard Herron

Reputation: 10102

Open, run embedded macro, save, close, repeat for folder of .xslm files

I am trying to run a macro that is embedded in each of 100 files without manually opening, running, saving, and closing them. The macro is only in the "target" files that I would like to run.

I found this SO question on how to do this and gotten it to work locally.

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

    Pathname = ThisWorkbook.Path
    Wildcard = "Target*.xlsm"
    Filename = Dir(Pathname & "\" & Wildcard)
    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 = "Goodbye World!"
    End With
End Sub

I put this VBA script in "Controller.xlsm" and I can run the DoWork() macro on a folder of target files with names like "Target1.xlsm", "Target2.xlsm", and so on. But I would like to replace DoWork() with a macro that is only in each of the targets (all the same macro, which calls software on this machine). I tried replacing DoWork() with things like OtherMacro and Filename!OtherMacro, but neither work (where OtherMacro is the identical macro in each of the "Target*.xlsm" files).

Is this possible? Can I run the macro in "Target*.xlsm" while looping through using a macro in "Controller.xlsm"?

It seems there's also the option to run the macro in "Target*.xlsm" and the others on open. If the macro in "Target*.xlsm" files requires 2 hours to run, will this cause problems? That is, will the macr in "Controller.xlsm" try to save and close "Target*.xlsm" before the work is done? Thanks!

Upvotes: 1

Views: 3476

Answers (2)

Excel Developers
Excel Developers

Reputation: 2825

I don't think you need to specify the module that the macro is in. This should work:

Application.Run "'" & wb.name & "'!DoStuff"

Note that the workbook name must be enclosed in single quotes. And I think the macro in the target workbook must be Public.

Upvotes: 1

NickSlash
NickSlash

Reputation: 5077

I had a play about with the link I added as a comment, it works but it does require you to change security permissions. If you know the name of the macro, and its location (module name) you should be able to just use Application.Run.

I tried without specifying the module name but it kept giving me an error. 

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

    Pathname = ThisWorkbook.Path
    Wildcard = "Target*.xlsm"
    Filename = Dir(Pathname & "\" & Wildcard)
    Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & "\" & Filename)
        Application.Run wb.name & "!Module1.DoStuff"
        wb.Close SaveChanges:=True
        Filename = Dir()
    Loop
End Sub

I'm sure Application.Run works like any other VBA sub, and that it will wait for it to complete before executing the next command but since there doesn't seem to be a way to return a value it might not? ( you are opening a new workbook in the same instance of excel so i think it should be ok )

Just re-read your question and I think you had the same question, if it is possible to modify your macros you could remove the loop and use a queue instead. Have the macro either return a value to indicate its finished, or use the above code to call a sub in the master sheet to execute the next item.

Upvotes: 1

Related Questions