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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

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:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,010
Messages
5,508,774
Members
408,692
Latest member
OptimalKR

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top