Application.Run returning error "400"

[paun_shotts]

Hi,

I have code in workbook A in a macro.
I have a button in WorkBook A that when pressed runs the below code.
I want to open another workbook, and then run the macro that is in workbookA on the workbook that was just opened.

Sub RunMacro()
Workbooks.Open "C:\Users\spotts\Documents\Excel VBA\po ang1324-converted.xls"
Application.Run "po ang1324-converted.xls!ApplyFormatting"
End Sub

The workbook "po ang1324-converted.xls" opens fine, then I get error message "400" and the macro does not run.
I believe the code is all correct, but I keep getting this error. If I comment out the line starting with "Application.Run" then I do not get the error message.
Can someone please help??

 

[Skyybot]

There is a lot to unpack here. Need to know which Workbook is already open, the Path of the Workbook that you want to open, and the location of the Macro that you want to run.

 

[paun_shotts]

There is a lot to unpack here. Need to know which Workbook is already open, the Path of the Workbook that you want to open, and the location of the Macro that you want to run.

Hi SkyBot, thanks for your response.
The Workbook that is open is: C:\Users\spotts\Documents\Excel VBA\po ang1323-converted.xlsm
The path of the workbook that I want to open is: "C:\Users\spotts\Documents\Excel VBA\po ang1324-converted.xls"

The Macro is in the workbook "po ang1323-converted.xlsm"
In this workbook, I have a button on Sheet 1, the button it linked to the code in the above post "Sub RunMacro()"
This code is opening "C:\Users\spotts\Documents\Excel VBA\po ang1324-converted.xls" with no issues.
But when it gets to the 2nd line, "Application.Run...." I get the error 400. If this line is commented out, that no error message, but obviously I dont want to comment it out, becasue I want to run the Macro in the newly opened workbook.

The start of the Macro that I want to run looks like this:

Sub ApplyFormatting()

Dim iRange As Range
Dim iCells As Range
Dim lastRow As Long

etc
etc
etc

 

[Skyybot]

Thank you for your response. Is it possible for you to show me the VBA project location of your Macro. I have submitted an image of what you VBA Project model may look like.

 

[Skyybot]

More specifically, is the Macro in the Workbook, or a Module, or a Worksheet? If in a Module, have you named the Module?

 

[paun_shotts]

More specifically, is the Macro in the Workbook, or a Module, or a Worksheet? If in a Module, have you named the Module?

The macro I want to run is in a Module, I've named the Module "ApplyFormatting"
In Sheet1, I have the code:

Sub RunMacro()
Workbooks.Open "C:\Users\spotts\Documents\Excel VBA\po ang1324-converted.xls"
Application.Run "po ang1324-converted.xls!ApplyFormatting"
End Sub

This is linked to a button on Sheet 1, when I press the button on Sheet1 I get the "400" error message
When I just press "play" on the code in Sheet1, I get error message "1004 - Application-defined or object-defined error"
In both scenarios, the correct Workbook is opening, but the Macro is not running.

 

[paun_shotts]

More specifically, is the Macro in the Workbook, or a Module, or a Worksheet? If in a Module, have you named the Module?

 

[paun_shotts]

I have run the macro by itself and it works perfectly with no issues.
I have run the below code by itself and the workbook opens with no issues:

Workbooks.Open "C:\Users\spotts\Documents\Excel VBA\po ang1324-converted.xls"

The issue is caused in this line of code:

Application.Run "po ang1324-converted.xls!ApplyFormatting"

There must be some error in the way that I am referencing the macro in the above line.
Ive spent hours researching online and from what I can find, this is how you do what I need to do, but for some reason its not working for me and im now clueless.

 

[paun_shotts]

SOLVED - I found another way of doing what I needed without using Application.Run

 

[Peter_SSs]

Good to hear you found a solution.
If you would like to post the solution then it is perfectly fine to mark that post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.
Note that the tick checkmark is not a 'Solved' button in our forums.