Open another workbook and run macro

[Noz2k]

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

 

[Trevor G]

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]

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

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]

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]

No the code is in a module

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]

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]

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

 

[Noz2k]

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

Sub SheetDataChart(Y As Long)

to

Sub SheetDataChart(Y As Long, wb As Workbook)

and

Set ws1 = Sheets("Sheet1")

to

Set ws1 = wb.Sheets("Sheet1")

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

Tried adding ,"Report_2012" to the line

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

but am getting a type mismatch error

 

[Noz2k]

Solved that little problem

had to change the line to

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