Application Run issue.

[bnpdkh]

here is my issue: I have 52 workbooks that re exactly the same, they load in data specific to each week of the year (same macros contained in each workbook). They are named "Work Week 1.xls, Work Week 2.xls......, Work Week 52.xls. I run an update macro from another work book that will open a specified workbook , select appropriate page and then run the required macro. To minimize the number of macros required to run the different updates needed i have directed the work book open to get address from a specific cell that updates when dates are changed. The code below works well for work week 1 but when workbook address opens "Work Week 8.xls" and application run fires an error comes up as the identified macro is in work week 1. I thought I had solution as listed below but I am missing code on the front end

Sub Test()
Workbooks.Open Filename:=Worksheets("Mechanical").Range("I15").Value _
        , UpdateLinks:=3
    Sheets("REMOVED SCOPE").Select
Application.Run "'WORK WEEK 1.xls'!NEW_SCOPE_T0"
' The above code works when workBook Work Week 1 is opened
' but will not run the exact same macro if Work Week 8 is opened.
'I found the code below R Debruin posted for when file name can be variable
'but do not know how to build code around it to make it work
Application.Run "'" & strFileName & "'!Scope_Unhide_Rows"
 
 
End Sub

 

[Luke M]

You don't share where the variable strFileName gets a value, but I'm guessing it has the full file path that was used to open the workbook. You just want the file's name. something like this should work better for you.

Sub Test()
Dim myWB As Workbook
Set myWB = Workbooks.Open(Filename:=Worksheets("Mechanical").Range("I15").Value _
        , UpdateLinks:=3)

myWB.Sheets ("REMOVED SCOPE")

Application.Run "'" & myWB.Name & "'!Scope_Unhide_Rows"


End Sub

 

[bnpdkh]

Great Luke, I tried Dim strFileName as string and a couple variations that did not work. Your suggestion works except a complie error comes up highlighting myWB. Sheets ("REMOVED SCOPE"). I tried Sheets select function and it seems to work well. Thank you for explaining the issue with full path being returned, any ideas why complie error is coming up?

 

[Luke M]

Oops, that's my error. I accidentally deleted the .Select at the end of the line.

Should be:

myWB.Sheets("REMOVED SCOPE").Select

Sorry about that...

 

[bnpdkh]

Solved, thanks for another win Luke, as always, much appreciated.