Running Macro from another workbook when Application.Run doesn't work

[USAMax]

I have been using the Application.Run for a while but something changed and now it won't work!

The only thing I changed is that I added the same command in another section of the procedure and I tried to use a goto to run this code but it skips the Application.Run without an error. Anybody have any ideas?

            Workbooks.Open Filename:=Range("InclusionTablePath") & _
                        "\" & Range("InclusionTableFile")   '          Open the AT Inclusion Table workbook
            Set wbAT = ActiveWorkbook                       '          Get the properties for the AT Inclusion Table workbook
            strTest = wbAT.Name
            Application.Run ("'" & strTest & "'!GetNewRequest") 'Update the Attrition Table List
            Application.DisplayAlerts = False               '          Disable Alerts and Warnings
            wbAT.Save                                       '          Save the workbook
            wbAT.Close                                      '          Close the workbook
            Application.DisplayAlerts = True                '          Enable Alerts and Warnings

 

[cerfani]

step through the code and verify the string value you are passing into the Run function is valid. Also you can avoid referencing the ActiveWorkbook to make your code easier to follow...

Set wbAT = Workbooks.Open Filename:=Range("InclusionTablePath") & "\" & Range("InclusionTableFile")

 

[USAMax]

Thank you cerfani,

I like your approach but your command gives me a Compile Error error saying, "Expected: end of statement" and highlighting Filename.

Working off of your idea I tried this and it would not open the workbook.

            Set wbAT = Range("InclusionTablePath") & "\" & Range("InclusionTableFile")
            Workbooks.Open wbAT

This skips the open line just as the Application.Run did.

 

[jkpieterse]

I'd say your original code should do just fine. What happens if you step through it?

 

[cerfani]

that code is wrong...

This is a string value...

Range("InclusionTablePath") & "\" & Range("InclusionTableFile")

You can't set it to wbAT. You should declare variables to make things easier to follow because you are mixing up types.

Dim strTest As String 'make sure you use these correctly
Dim wbAT As Workbook

Set wbAT = Workbooks.Open Filename:=Range("InclusionTablePath") & "\" & Range("InclusionTableFile")   '          Open the AT Inclusion Table workbook
strTest = wbAT.Name
Application.Run ("'" & strTest & "'!GetNewRequest") 'Update the Attrition Table List
Application.DisplayAlerts = False               '          Disable Alerts and Warnings
wbAT.Save                                       '          Save the workbook
wbAT.Close                                      '          Close the workbook
Application.DisplayAlerts = True                '          Enable Alerts and Warnings

 

[USAMax]

cerfani,

The issue was that I added a call to a private sub within the sub GetNewRequest. My assumption is that an routine from another workbook should not be allowed to access a private sub in another workbook either directly or indirectly.

Thanks again for the help. Would still like to know how to open the workbook and set the variable at the same time the way you intended.

 

[cerfani]

Workbooks.Open returns a workbook reference so look at the code i posted, that is all you do... You use the Set keyword and save the value to a workbook variable.

Dim wbk As Workbook
Set wbk = Workbooks.Open "C:\something.xlsx"

 

[jkpieterse]

@cerfani: ALmost correct, but it needs some brackets:

Set wbAT = Workbooks.Open(Filename:=Range("InclusionTablePath").Value & "\" & Range("InclusionTableFile").Value)

And I prefer not to rely on the range objects default property and explicitly use .Value so I added that as well.

 

[cerfani]

Oh thanks, I usually rely on the compiler to say something when it comes to using or not using () with function calls

 

[USAMax]

Thank you cerfani and jkpieterse!

cerfani, if it wasn't for you I never would have tried to set the variable while opening the workbook.

jkpieterse, I very much appreciate your completing the final step in cerfani's solution.

Thank you both!