small help with VBA, run a Sub within another workbook

[RompStar]

In Sheet(1) on the active.workbook where I play this VBA from (below), the sheet is named as "Log", starting on row 2 in the A column, is:

\\full path-networkpath+directory & workbook name to open.

As In:

\\network-drive\path\directory\test1.xls
\\network-drive\path\directory\test2.xls

In each of the workbooks to open: test1 and test2 [in this test], there is a module with a Sub named ADD_BUTTONS(), this VBA is supposed to open all the workbooks one-after another from A2 to last row and execute the ADD_BUTTONS code within its self and close, next... The ADD_BUTTONS has code to save the workbook operating on already after it does its thing, so there is no need for this script to change anything as far as Save.

So, it opens it, but the Module Sub don't seem to run, what am I doing wrong ?

That is the only part not working.

Thank you for the help!


Sub Open_WrkBks_Log_Run_ModuleSub_Within()

Dim wsLogName As String
Dim wsLog As Worksheet
Dim wsLogRange As Range
Dim FileToOpen As String
Dim RangeCell As Object

wsLogName = ActiveWorkbook.Name
Set wsLog = ActiveSheet

With wsLog
Sheets("Log").Activate
lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
Set wsLogRange = Range("A2:A" & lastrow)
End With

For Each Value2 In wsLogRange.Cells
FileToOpen = Value2
On Error Resume Next

Workbooks.Open Filename:=FileToOpen
Application.Run (ADD_BUTTONS)

ActiveWorkbook.Close SaveChanges:=False

Next Value2

MsgBox "Finished Looping All workbooks from Log and running their Sub within"

End Sub

 

[RompStar]

I changed it a little bit and it seems to know the file name of the workbook to open now, because I see that WrkBookName has test.xls now assigned to it and it worked using this test:

Application.Run "'test.xls'!ADD_BUTTONS"


I just need help how to insert the value of WrkBookName, how does this work ?

Application.Run "'&WrkBookName&'!ADD_BUTTONS"

not sure how to merge that in there....


x = 1

For Each Value2 In wsLogRange.Cells

FileToOpen = Value2
WrkBookName = Value2(x, 2)
On Error Resume Next

Workbooks.Open Filename:=FileToOpen
Application.Run "'WrkBookName'!ADD_BUTTONS"

ActiveWorkbook.Close SaveChanges:=False

x = 1 + 1

Next Value2

 

[RompStar]

To explain this further, in B2 and to last row riding with A2 to last row, in B2 I put this formula:

=RIGHT(A2,LEN(A2)-FIND("^^",SUBSTITUTE(A2,"\","^^",LEN(A2)-LEN(SUBSTITUTE(A2,"\","")))))

this strips the filename.xls from the \\fullpath\filename.xls mix in A

and then I adjusted the code a little bit:



Sub Process_Logs()

Dim wsLogName As String
Dim wsLog As Worksheet
Dim wsLogRange As Range
Dim FileToOpen, WrkBookName As String
Dim RangeCell As Object

wsLogName = ActiveWorkbook.Name
Set wsLog = ActiveSheet

With wsLog
Sheets("Log").Activate
lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
Set wsLogRange = Range("A2:B" & lastrow) ' changed the Range here to capure B column too
End With

x = 1

For Each Value2 In wsLogRange.Cells

FileToOpen = Value2
WrkBookName = Value2(x, 2)
On Error Resume Next

Workbooks.Open Filename:=FileToOpen
Application.Run "'WrkBookName'!ADD_BUTTONS"

ActiveWorkbook.Close SaveChanges:=False

x = 1 + 1

Next Value2

MsgBox "Finished Looping All workbooks from Log and running their Sub within"

End Sub




Anyways, this last part is not working: Application.Run "'WrkBookName'!ADD_BUTTONS"

it works when I put there test.xls and the WrkBookName = test1.txt and test2.txt with each loop, I put a watch over the variable, I think I am just not properly formatting that line of code.

 

[RompStar]

ok, figured it out:

Application.Run (variable & "!MacroName")