Open Macros from another workbook

A

Analytic

New Member
Joined
Oct 31, 2015
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
Hi,

Here is a code:

Code: 
[COLOR=#333333]Private Sub CommandButton1_Click()


Dim varNames1 As Variant
Dim varNames2 As Variant


Dim lngArr As Long
Dim wb As Workbook


varNames1 = Array("FIRST\GRAND EVALUATIONS.xlsm")
For lngArr = LBound(varNames1) To UBound(varNames1)
  Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & varNames1(lngArr))
  Application.Run "'GRAND EVALUATIONS.xlsm'!Macro1"
  wb.Close True
  Set wb = Nothing
Next lngArr




varNames2 = Array("FIRST - Copy\GRAND EVALUATIONS.xlsm")
For lngArr = LBound(varNames2) To UBound(varNames2)
  Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & varNames2(lngArr))
  Application.Run "'GRAND EVALUATIONS.xlsm'!Macro1"
  wb.Close True
  Set wb = Nothing
Next lngArr


ActiveWorkbook.Save
ActiveWorkbook.Close


End Sub



[/COLOR]

Firstly the above code is supposed to open Macro1 of

Code: 
[COLOR=#333333]varNames1 = Array("FIRST\GRAND EVALUATIONS.xlsm")
[/COLOR][COLOR=#333333]For lngArr = LBound(varNames1) To UBound(varNames1)[/COLOR]
[COLOR=#333333]  Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & varNames1(lngArr))[/COLOR]
[COLOR=#333333]  Application.Run "'GRAND EVALUATIONS.xlsm'!Macro1"[/COLOR]
[COLOR=#333333]  wb.Close True[/COLOR]
[COLOR=#333333]  Set wb = Nothing[/COLOR]
[COLOR=#333333]Next lngArr[/COLOR]


and it does that.

Secondly it was also expected to open Macro1 of

Code: 
[COLOR=#333333]varNames2 = Array("FIRST - Copy\GRAND EVALUATIONS.xlsm")
[/COLOR][COLOR=#333333]For lngArr = LBound(varNames2) To UBound(varNames2)[/COLOR]
[COLOR=#333333]  Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & varNames2(lngArr))[/COLOR]
[COLOR=#333333]  Application.Run "'GRAND EVALUATIONS.xlsm'!Macro1"[/COLOR]
[COLOR=#333333]  wb.Close True[/COLOR]
[COLOR=#333333]  Set wb = Nothing[/COLOR]
[COLOR=#333333]Next lngArr

[/COLOR]

but it doesn't even open the file varNames2 = Array("FIRST - Copy\GRAND EVALUATIONS.xlsm").

Please provide me with some solutions to solve this problem.

Thank you:confused:
 
Last edited:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Analytic said:
Hi,

Here is a code:

Code: 
[COLOR=#333333]Private Sub CommandButton1_Click()


Dim varNames1 As Variant
Dim varNames2 As Variant


Dim lngArr As Long
Dim wb As Workbook


varNames1 = Array("FIRST\GRAND EVALUATIONS.xlsm")
For lngArr = LBound(varNames1) To UBound(varNames1)
  Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & varNames1(lngArr))
  Application.Run "'GRAND EVALUATIONS.xlsm'!Macro1"
  wb.Close True
  Set wb = Nothing
Next lngArr




varNames2 = Array("FIRST - Copy\GRAND EVALUATIONS.xlsm")
For lngArr = LBound(varNames2) To UBound(varNames2)
  Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & varNames2(lngArr))
  Application.Run "'GRAND EVALUATIONS.xlsm'!Macro1"
  wb.Close True
  Set wb = Nothing
Next lngArr


ActiveWorkbook.Save
ActiveWorkbook.Close


End Sub



[/COLOR]

Firstly the above code is supposed to open Macro1 of

Code: 
[COLOR=#333333]varNames1 = Array("FIRST\GRAND EVALUATIONS.xlsm")
[/COLOR][COLOR=#333333]For lngArr = LBound(varNames1) To UBound(varNames1)[/COLOR]
[COLOR=#333333]  Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & varNames1(lngArr))[/COLOR]
[COLOR=#333333]  Application.Run "'GRAND EVALUATIONS.xlsm'!Macro1"[/COLOR]
[COLOR=#333333]  wb.Close True[/COLOR]
[COLOR=#333333]  Set wb = Nothing[/COLOR]
[COLOR=#333333]Next lngArr[/COLOR]


and it does that.

Secondly it was also expected to open Macro1 of

Code: 
[COLOR=#333333]varNames2 = Array("FIRST - Copy\GRAND EVALUATIONS.xlsm")
[/COLOR][COLOR=#333333]For lngArr = LBound(varNames2) To UBound(varNames2)[/COLOR]
[COLOR=#333333]  Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & varNames2(lngArr))[/COLOR]
[COLOR=#333333]  Application.Run "'GRAND EVALUATIONS.xlsm'!Macro1"[/COLOR]
[COLOR=#333333]  wb.Close True[/COLOR]
[COLOR=#333333]  Set wb = Nothing[/COLOR]
[COLOR=#333333]Next lngArr

[/COLOR]

but it doesn't even open the file varNames2 = Array("FIRST - Copy\GRAND EVALUATIONS.xlsm").

Please provide me with some solutions to solve this problem.

Thank you:confused:
Click to expand...
In the second part try:
Rich (BB code): 
varNames2 = Array("FIRST - Copy\GRAND EVALUATIONS.xlsm")
For lngArr = LBound(varNames2) To UBound(varNames2)
  Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & varNames2(lngArr))
  Application.Run "'FIRST - Copy\GRAND EVALUATIONS.xlsm'!Macro1" 
  wb.Close True   
Set wb = Nothing 
Next lngArr
 
Upvote 0
Do you get an error? If yes, what is the error description?

Do you have a subfolder called exactly "FIRST - Copy" ?


This is a shorter version. It does not fix your problem of the 2nd workbook not opening.

Code: 
Private Sub CommandButton1_Click()
    
    Dim varNames1 As Variant
    Dim lngArr As Long
    Dim wb As Workbook
    
    varNames1 = Array("FIRST", "FIRST - Copy")
    For lngArr = LBound(varNames1) To UBound(varNames1)
      Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & varNames1(lngArr) & "\GRAND EVALUATIONS.xlsm")
      Application.Run "'GRAND EVALUATIONS.xlsm'!Macro1"
      wb.Close True
      Set wb = Nothing
    Next lngArr
    
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    
End Sub
 
Upvote 0
JoeMo said:
In the second part try:
Rich (BB code): 
varNames2 = Array("FIRST - Copy\GRAND EVALUATIONS.xlsm")
For lngArr = LBound(varNames2) To UBound(varNames2)
  Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & varNames2(lngArr))
  Application.Run "'FIRST - Copy\GRAND EVALUATIONS.xlsm'!Macro1" 
  wb.Close True   
Set wb = Nothing 
Next lngArr
Click to expand...

Hi,

I did tried both of the solutions, yet no change. The problem remains unchanged.
It just seems impossible to even run that second file.:mad:
Thanks
 
Upvote 0
Hi AlphaFrog

There is no error code.

The file descriptions are given below.

Main workbook=C:\CALCULATIONS\ENTRY BOARD.xlsm
First workbook=C:\CALCULATIONS\FIRST\GRAND EVALUATIONS.xlsm
Second workbook=C:\CALCULATIONS\FIRST - Copy\GRAND EVALUATIONS.xlsm

Thanks
 
Upvote 0
Is there a line in Macro1 that just says End (not End Sub)? If yes, that will halt all code execution.

Maybe you should post your Macro1 code.
 
Upvote 0
AlphaFrog said:
Is there a line in Macro1 that just says End (not End Sub)? If yes, that will halt all code execution.

Maybe you should post your Macro1 code.
Click to expand...


Code: 
Sub Macro1()
Dim varNames1 As Variant




Dim lngArr As Long
Dim wb As Workbook




varNames1 = Array("CAL1\RESULTS.xlsx")
For lngArr = LBound(varNames1) To UBound(varNames1)
  Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & varNames1(lngArr))
  wb.Close True
  Set wb = Nothing
Next lngArr






    ActiveWorkbook.Save
    ActiveWorkbook.Close
    
End Sub
 
Upvote 0
What is the purpose of opening and closing Results.xlsx?
Does the workbook Results.xlsx close?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
VBA , To consolidate two worksheet_chang events or to add events to another one
Replies
0
Views
407
ROUKWA
R
G
  • Question
Select an Open workbook and apply a macro1 contained in the new book to the new book
Replies
1
Views
146
LazyBug
LazyBug
S
  • Question
Saving as dialog box. Application.DisplayAlerts not working
Replies
7
Views
89
Serafin54
S
C
  • Solved
Macro works for one file, does not work for all files in the directory
2
Replies
13
Views
532
Alex Blakenburg
A
G
  • Question
Open workbooks in folder and run Macro in each book
Replies
7
Views
392
glynn1969
G

Forum statistics

Threads
1,215,248
Messages
6,123,866
Members
449,129
Latest member
krishnamadison

Share this page

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
Back
Top