CODEWITHSUNDEEP
vbaexcel
Clauric
Clauric

Reputation: 1896

Open new workbook, and run macro, using button

I have a workbook that runs a large multitude of macros, all based on which button is clicked.

I need one of the macro to open a new workbook, and import two files into the workbook (that part I can do).

The second part is that I need it to run a particular macro, in the new workbook. The macro would be stored in the original workbook.

I've seen some suggestions that I need to use APPLICATION.RUN, which has the parameters of having to choose a workbook and name of the routine.

Does this method work?

Assuming it does:

For the workbook, do I need to specify the file path, or just the workbook name?

For the macro, do I need to make the macro public?

Do I need to specify the module it is in?

Are there any other parameters I need to specify to get this method to work?

Is there another method that might work, if APPLICATION.RUN does not work?

Upvotes: 0

Views: 742

Answers (2)

R3uK
R3uK

Reputation: 14547

What you need to use is :

Application.Run "'FileName.xlsm'!MacroName", "Parameters"

You don't need to specify the path if your workbook is already open, you don't need to specify the module as you can't have doubloons names for different procedures, and there are no other parameters needed.

The procedure doesn't need to be public (let me know if it does), and for alternative methods in VBA, there is none (as the Call method only work in the same workbook), but there are some in VB if this doesn't work out.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96791

Here is a very short example that you can adapt:

Sub demo()
    Dim Original As Workbook
    Dim Created As Workbook
    Set Original = ThisWorkbook

    '   create a new workbook

    Workbooks.Add
    Set Created = ActiveWorkbook

    '    go back

    Original.Activate

    'make a change in the newly created workbook

    Created.Sheets("Sheet1").Range("A1").Value = "whatever"

    '   save the newly created workbook

    Created.Save
    Created.Close

End Sub

Upvotes: 1

Related Questions