small help with VBA, run a Sub within another workbook

RompStar

Well-known Member
Joined
Mar 25, 2005
Messages
1,200
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

RompStar

Well-known Member
Joined
Mar 25, 2005
Messages
1,200
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

Well-known Member
Joined
Mar 25, 2005
Messages
1,200
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,132,823
Messages
5,655,479
Members
418,205
Latest member
jacc99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top