Open another workbook and run macro

Noz2k

Well-known Member
Joined
Mar 15, 2011
Messages
688
Ok, so I currently have a workbook (Report2011) which records data each week (in a new sheet), and then dependant upon the choices made in sheet1, constructs a summary sheet which returns the averages over the time period selected, as well as showing graphs which illustrate the trend over time for each category in the summary.

What I would now like to do, is to create an almost identical workbook, which corresponds to the following year (Report2012). However I want to add a function where by the user can select a checkbox on sheet1 (We'll call it cbx2011), and when cbx2011 is true, the 2 procedures related to building the graph (to illustrate trend over time) would also be ran in Report2011, however the results would be added to the same graph in Report2012, to enable an easy comparison between the 2 years.

So the first question I have is, how do I open a closed workbook, and then run a procedure in that workbook?


Thanks in advance for any help
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
Try something like this

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> openBk()<br>Workbooks.Open "C:\Address.xls" <SPAN style="color:#007F00">'Change address and name</SPAN><br>Application.Run "MacroName" <SPAN style="color:#007F00">'Add your macro name here</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Noz2k

Well-known Member
Joined
Mar 15, 2011
Messages
688
Thanks Trevor.

I tried your way and I get the following error

Run-time Error '1004':

Cannot run the macro '!SheetDataChart(6)'. The macro may not be available in this workbook or all macros may be disabled.

I also tried this code

Code:
Sub Open2011()
     'Macro purpose:  To use the application.run method to execute
     'a function or macro (with arguments) from another workbook
 
    Dim PathToFile As String, _
    NameOfFile As String, _
    wbTarget As Workbook, _
    MyResult As Variant, _
    CloseIt As Boolean
 
     'Set file name and location.
    NameOfFile = "Report_2011.xlsm"
    PathToFile = "C:\Users\Me\Desktop"
 
     'Attempt to set the target workbook to a variable.  If an error is
     'generated, then the workbook is not open, so open it
    On Error Resume Next
    Set wbTarget = Workbooks(NameOfFile)
 
    If Err.Number <> 0 Then
         'Open the workbook
        Err.Clear
        Set wbTarget = Workbooks.Open(PathToFile & "\" & NameOfFile)
        CloseIt = True
    End If
 
     'Check and make sure workbook was opened
    If Err.Number = 1004 Then
        MsgBox "Sorry, but the file you specified does not exist!" _
        & vbNewLine & PathToFile & "\" & NameOfFile
        Exit Sub
    End If
 
    On Error GoTo 0
 
     'Run the function.  Update the "FunctionName" to the name of your function
     'and change 1 & 2 to the arguments you need to pass to the function
    MyResult = Application.Run(wbTarget.Name & "!SheetDataChart", 6)
 
     'Give user the results
    MsgBox MyResult
 
    'Application.Run(wbTarget.Name & "!BuildGraph",6)
 
    If CloseIt = True Then
         'If the target workbook was opened by the macro, close it
        wbTarget.Close savechanges:=False
    Else
         'If the target workbook was already open, reactivate this workbook
        ThisWorkbook.Activate
    End If
 
End Sub

and get a similar error message

Run-time error '1004':

Cannot run the macro 'Report_2011.xlsm!SheetDataChart'. The macro may not be available in this workbook or all macros may be disabled.

Any ideas? 'SheetDataChart' is definitely the name of the macro, and I'm trying to set the parameter for Y (type Long) as 6
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
That looks as though it is a sheet name?

Have you placed the code behind a worksheet?

Can you post the macro code for '!SheetDataChart(6)'
 

Noz2k

Well-known Member
Joined
Mar 15, 2011
Messages
688

ADVERTISEMENT

No the code is in a module

Code:
Sub SheetDataChart(Y As Long)
 
Dim ws1         As Worksheet, _
    ws2         As Worksheet, _
    wsm         As Worksheet, _
    p           As String, _
    NumLoops    As Long, _
    WeekSNum    As Long, _
    X           As Long, _
    DT          As Date, _
    DF          As Date, _
    Yearstart   As Date
 
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set wsm = Sheets("Master")
 
ws1.Range("C5") = CDate(Sheets("Sheet1").txtDateFrom.Value)
ws1.Range("G5") = CDate(Sheets("Sheet1").txtDateTo.Value)
 
DF = (ws1.Range("C5") + 7) - (Weekday((ws1.Range("C5") + 7), 2))
DT = (ws1.Range("G5") + 7) - (Weekday((ws1.Range("G5") + 7), 2))
 
Yearstart = ws1.Range("H23")
 
With wsm
   .Cells.Clear
End With
 
NumLoops = (DT - DF) / 7 + 1
''Sets the value for NumLoops based on 2 date values in sheet1
 
    WeekSNum = (DF - Yearstart) / 7 + 1
''Sets the value for WeekSNum based on 2 values in sheet1
 
    X = 0
''Sets the start value for X
 
Do Until X = NumLoops
p = "Week" & WeekSNum + X
 
On Error Resume Next
 
With Sheets(p).Range("A1:A200")
Set rng = .Find(ws2.Range("A" & Y))
rng1 = rng.Row
End With
 
If Sheets(p).Range("S3") = "Total" Then
    Sheets(p).Range("C" & rng1, "Q" & rng1).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("S" & rng1).Copy wsm.Range("AE1").Offset(X, 0)
ElseIf Sheets(p).Range("U3") = "Total" Then
    Sheets(p).Range("C" & rng1, "S" & rng1).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("U" & rng1).Copy wsm.Range("AE1").Offset(X, 0)
ElseIf Sheets(p).Range("W3") = "Total" Then
    Sheets(p).Range("C" & rng1, "U" & rng1).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("W" & rng1).Copy wsm.Range("AE1").Offset(X, 0)
ElseIf Sheets(p).Range("Y3") = "Total" Then
    Sheets(p).Range("C" & rng1, "X" & rng1).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("Y" & rng1).Copy wsm.Range("AE1").Offset(X, 0)
ElseIf Sheets(p).Range("AA3") = "Total" Then
    Sheets(p).Range("C" & rng1, "Z" & rng1).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("AA" & rng1).Copy wsm.Range("AE1").Offset(X, 0)
ElseIf Sheets(p).Range("AC3") = "Total" Then
    Sheets(p).Range("C" & rng1, "AB" & rng1).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("AC" & rng1).Copy wsm.Range("AE1").Offset(X, 0)
ElseIf Sheets(p).Range("AE3") = "Total" Then
    Sheets(p).Range("C" & rng1, "AD" & rng1).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("AE" & rng1).Copy wsm.Range("AE1").Offset(X, 0)
End If
 
wsm.Range("CZ1").Offset(X, 0) = p
 
X = X + 1
 
On Error GoTo 0
 
Loop
 
End Sub
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
If the name contains blanks, you need to enclose the file name
within single quotes. E.g.,

Application.Run "'Other Workbook Name.xls'!MacroName"
 

Noz2k

Well-known Member
Joined
Mar 15, 2011
Messages
688

ADVERTISEMENT

and if there aren't any spaces in the Workbook name?
 

Noz2k

Well-known Member
Joined
Mar 15, 2011
Messages
688
Ok, so I don't know what I changed, but I played around with it a bit and it seems to be working now.

So I think the next step towards my aim, is adding a parameter which specifies the workbook name into the SheetDataChart(Y As Long) code that I posted earlier.

And then amending the code so that when ran from the Report_2012, it takes the values from sheet1 in Report_2012 rather than sheet1 in Report_2011.


I have changed the line
Code:
Sub SheetDataChart(Y As Long)

to
Code:
Sub SheetDataChart(Y As Long, wb As Workbook)

and

Code:
Set ws1 = Sheets("Sheet1")

to

Code:
Set ws1 = wb.Sheets("Sheet1")

Is this the correct way of using the workbook as a parameter?

Tried adding ,"Report_2012" to the line

Code:
MyResult = Application.Run(wbTarget.Name & "!SheetDataChart", 6)

but am getting a type mismatch error
 

Noz2k

Well-known Member
Joined
Mar 15, 2011
Messages
688
Solved that little problem

had to change the line to

Code:
MyResult = Application.Run(wbTarget.Name & "!SheetDataChart", 6, Workbooks("Report_2012"))

Now to figure out how to add these values as new series on my chart
 

Watch MrExcel Video

Forum statistics

Threads
1,123,078
Messages
5,599,632
Members
414,326
Latest member
kfg1287

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